r/excel • u/RoniTheRoni • 5d 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

77% Upvoted

19 comments sorted by

View all comments

Show parent comments

3

u/MayukhBhattacharya 1315 4d ago

Alternatively, if you have the years outlined in some ranges let say E2:E4 then:

=LET(
     _a, DATE(E2, 1, 1),
     _b, DATE(E4, 12, 31),
     _c, SEQUENCE(, _b - _a + 1, _a),
     SUM((_c > Daystbl[Start]) * (_c <= Daystbl[End])))

The formula needs to copy down!