# 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 `REDUCE`

d 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.