Calculating days overseas for India registration

One of the more complicated aspects of living in India is the need to keep track of the number of days that I’m staying in each country. Both the US and India need this information for tax purposes. This is complicated by the fact that the US uses the calendar year for personal taxes, while India uses a fiscal year for both personal and business taxes. In addition, for those on business visas, India normally limits the duration of each stay to 180 continuous days. Stays can be extended past 180 days if registration with the Foreign Regional Registration Office is approved. However, registration is only possible after 180 cumulative days in one calendar year. Details here. Since it’s days per calendar year, a long stay that starts towards the end of the year resets the counter at the beginning of the next year for the purposes of the registration calculation. This means that you can’t apply for registration until the second half of the year (best case, assuming an arrival on January 1). In my experience, registration extends the allowed stay by another 6 months. This means that there’s some incentive to plan trips and anticipate registration so as to limit required trips to once a year instead of twice a year.

Managing the madness #

Between the taxes, visa, and registration requirements, it’s really complicated to keep the days straight. To protect my sanity, I developed a spreadsheet (shared copy with fake data) that calculates all of these days using only a single list of trip dates.

Here’s an example of some trips (again, fake data):

Using this data, the spreadsheet calculates the days for both tax years:

It also calculates the days remaining until the first opportunity to apply for registration. In this example, for 2023, the first possible registration date was Sept 23. That’s because the first stay was 178 days (due to the requirement to leave before 180 days), and the second stay quickly completed the remaining required days. In this example, the next trip was to the US, but if registration was approved perhaps a trip wouldn’t have been required until March. For 2024, registration will be possible on August 12 at the earliest with 28 days remaining until then.

How it works #

For these calculations, I’m using some rather complicated array and lambda functions. Here’s the formula to calculate the number of days in the US for a particular calendar year:

=SUM(MAP(FILTER($A$3:$A,$C$3:$C="US"), FILTER($B$3:$B,$C$3:$C="US"), LAMBDA(start, end,
   LET(min_end,MIN(end, DATE($G3,12,31)),
       max_start,MAX(start,DATE($G3,1,1)),
       (min_end-max_start)*(min_end>max_start)))))

The first part filters the trips to only those in the US. The LAMBDA function takes the start and end dates for each trip and calculates the overlapping dates for the calendar year specified on that row (2023, 2024, etc). Finally, the SUM adds them together to get the total for that calendar year.

The calculation for the remaining days before registration is more complicated:

=REDUCE({180,IFERROR(0/0)}, SEQUENCE(ROWS($A$3:$B), 1, 3, 1), LAMBDA(total, row,
   LET(remaining, INDEX(total, 1, 1),
       last, INDEX(total, 1, 2),
       start, INDEX(A:A, row, 1),
       end, INDEX(B:B, row, 1),
       min_end, MIN(end, DATE($O3,12,31)),
       max_start, MAX(start,DATE($O3,1,1)),
       days, (min_end-max_start)*(min_end>max_start),
       IF(AND(INDEX(C:C, row, 1) = "India", days > 0, remaining > 0), {MAX(0, remaining - days), max_start + MIN(remaining, days)}, total))))

First, this uses REDUCE to process all the trips in sequence. To start, we get an array containing the row indexes for all the trips using SEQUENCE. This list of row indexes will be REDUCEd into two values: the number of days remaining until registration is possible and the last date in India for each year. The processing starts with the initial values {180, EMPTY}, 180 for the number of days required before registration and an empty position that will track the last date. As an aside, we’re using a weird IFERROR hack that forces a division by zero error to get an empty value.

Next, using an overlap formula similar to the previous one, for each trip, we determine the number of days in India for the calendar year. Trips in India for this calendar year will have days greater than zero.

Finally, we tally up the trips until we pass the 180 day requirment. At that point we stop, because we only want to know the earliest possible date for registration.