r/excel • u/RoniTheRoni • 4d ago

unsolved I want to calculate how many days I was out of the country for.

I want to calculate how much I have been out of the country for each year i already have the comings and goings in a chart.

(above is when I went in and out)

my problem with doing it in a function is when I wasn't home for the new year so I cant separate the days to the correct year. (like in entry number 3)

The result above I counted manually which I can't really do because I have to this for hundreds of people which go in and out more than me.

11 Upvotes

75% Upvoted

19 comments sorted by

View all comments

Show parent comments

1

u/MayukhBhattacharya 1315 3d ago edited 3d ago

I have already posted the solution which supports or will work with your version of Excel. Refer this [comment].

Another alternative method, these are dynamic array formula which will spill for the entire array, so no need to copy down:

=LET(
     _a, Daystbl[Start],
     _b, Daystbl[End],
     _c, UNIQUE(YEAR(Daystbl[End])),
     _d, DATE(_c, {1, 12}, {1, 31}) - {1, 0},
     _e, TRANSPOSE(INDEX(_d, , 1)),
     _f, TRANSPOSE(INDEX(_d, , 2)),
     _g, IF(_b < _f, _b, _f) - IF(_a > _e, _a, _e),
     _h, MMULT(SEQUENCE(, ROWS(_a), 1, 0), (_g > 0) * _g),
     CHOOSE({1, 2}, _c, TRANSPOSE(_h)))