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

[u/RoniTheRoni]

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.

[u/AutoModerator]

/u/RoniTheRoni - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

Try using the following formula:

=LET(
     _a, Daystbl[Start],
     _b, Daystbl[End],
     _c, SEQUENCE(, MAX(_b) - MIN(_a) + 1, MIN(_a)),
     _d, TOCOL(IFS((_c > _a) * (_c <= _b), _c), 3),
     _e, GROUPBY(YEAR(_d), _d, ROWS, , 0, -1),
     VSTACK({"Years", "Days Out Of The Country"}, _e))

Please make sure to change the name of the table per your suit, for the example above I have named the table as Daystbl.

We can skip the IFS() since we are dealing with dates aka stores as numbers in excel, so:

=LET(
     _a, Daystbl[Start],
     _b, Daystbl[End],
     _c, SEQUENCE(, MAX(_b) - MIN(_a) + 1, MIN(_a)),
     _d, TOCOL(_c/((_c > _a) * (_c <= _b)), 3),
     _e, GROUPBY(YEAR(_d), _d, ROWS, , 0, -1),
     VSTACK({"Years","Days Out Of The Country"}, _e))

[u/Inuluni → u/MayukhBhattacharya]

Hi MayukhBhattacharya, thanks for the formula. Amazing solution. Just a question, why _c > _a and not _c >= _a in the _d variable?

[u/MayukhBhattacharya → u/Inuluni]

Thank You Very Much for your kind words, I appreciate the same. So, for your question the logic is using _c > _a, is the departure day isn't counted, since you're still in the country for part of the day. Using >= would count both the departure and the return days. Either works, you need to pick the rule you want and apply it accordingly per your suit.

Edit: Sorry, I just realized you're not the OP, kindly ignore my earlier comment about Solution Verified. Thanks!

[u/Inuluni → u/MayukhBhattacharya]

Ah ok. I did not realize that. Your explanation made sense. Thank you.

[u/MayukhBhattacharya → u/Inuluni]

No problem at all. Thanks to you as well!

[u/CiekaMedinho → u/MayukhBhattacharya]

that trick with dividing by the boolean to force errors and then using TOCOL to strip them is clever tbh

[u/MayukhBhattacharya → u/CiekaMedinho]

Thank You SO Much!

[u/RoniTheRoni → u/MayukhBhattacharya]

good to know i also had a mistake in ny counting thank you

[u/RoniTheRoni → u/MayukhBhattacharya]

my office doesn't use the current version of excel so can't use the tocol, vstack and groupby functions is there a way around it or am i screwed?

edit: office plus 2021 version 2108 build 14332.20145

[u/MayukhBhattacharya → u/RoniTheRoni]

What is your version of Excel?

[u/MayukhBhattacharya → u/RoniTheRoni]

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!

[u/carbonizedtitanium → u/RoniTheRoni]

try web excel (excel in browser). you have to upload to onedrive and open from the browser

[u/RoniTheRoni → u/carbonizedtitanium]

we are on a private network so that isn't possible either

[u/MayukhBhattacharya → u/RoniTheRoni]

Assuming you are using Excel 2021+ then try one of the following methods, these are dynamic array formula which will spill for the entire array, so no need to copy down:

• Without Header:

=LET(
     _a, Daystbl[Start],
     _b, Daystbl[End],
     _c, SEQUENCE(, MAX(_b) - MIN(_a) + 1, MIN(_a)),
     _d, YEAR(_c),
     _e, UNIQUE(TRANSPOSE(_d)),
     _f, (_c > _a) * (_c <= _b),
     _g, MMULT(SEQUENCE(, ROWS(_a), , 0), _f),
     SORT(CHOOSE({1, 2}, _e, MMULT(N(_e = _d), TRANSPOSE(_g))), 2))

• With Header:

=LET(
     _a,  Daystbl[Start],
     _b,  Daystbl[End],
     _c,  SEQUENCE(, MAX(_b) - MIN(_a) + 1, MIN(_a)),
     _d,  YEAR(_c),
     _e,  UNIQUE(TRANSPOSE(_d)),
     _f,  (_c > _a) * (_c <= _b),
     _g,  MMULT(SEQUENCE(, ROWS(_a), , 0), _f),
     _h,  SORT(CHOOSE({1, 2}, _e, MMULT(N(_e = _d), TRANSPOSE(_g))), 2),
     _i,  SEQUENCE(ROWS(_h) + 1),
     IF(_i = 1, {"Years", "Days Out Of The Country"},
                INDEX(_h, IF(_i = 1, 1, _i -1), {1, 2})))

[u/MayukhBhattacharya → u/RoniTheRoni]

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)))

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSE	Chooses a value from a list of values
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
DATE	Returns the serial number of a particular date
DAYS	Excel 2013+: Returns the number of days between two dates
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
IF	Specifies a logical test to perform
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX	Returns the maximum value in a list of arguments
MIN	Returns the minimum value in a list of arguments
MMULT	Returns the matrix product of two arrays
ROWS	Returns the number of rows in a reference
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SORT	Office 365+: Sorts the contents of a range or array
SUM	Adds its arguments
TOCOL	Office 365+: Returns the array in a single column
TRANSPOSE	Returns the transpose of an array
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
YEAR	Converts a serial number to a year

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49513 for this sub, first seen 13th Oct 2026, 22:19] [FAQ] [Full list] [Contact] [Source code]}

[u/real_barry_houdini]

How do you want to count, are you including the start date or the end date (or both)? The following counts the end date but not the start, you can adjust the >= and <= as required

=LET(s,DATE(F2,1,1),e,DATE(F2+1,1,0),d,SEQUENCE(e-s+1,,s),
SUM(COUNTIFS(B$2:B$10,"<"&d,C$2:C$10,">="&d)))

Works in Excel 2021 where F2 is the year number

[u/carbonizedtitanium]

this setup should work for all versions of Excel:

assuming that you were *outside* the country on both the start the date *and* the end date, put this in D3:

=MAX(0, MIN($C3, DATE(D$2, 12, 31)) - MAX($B3 + $A$1, DATE(D$2, 1, 1)) + 1)

and drag down and drag towards the right.

if you want to exclude either the start date or end date (or both) from the calculation, just enter how many days you wanna subtract from the calculations in A1; e.g. if you put 2 in A1 then you're basically excluding the start date and end date from the count.

[u/this_guy9999]

There’s literally a function for this; DAYS.

ETA: if you know the dates you were in the country, just put that in its own schedule and subtract that. Y’all make shit too complicated.

[u/foosion → u/this_guy9999]

DAYS doesn't separate the total days into the correct year when an entry spans two years. As OP put it:

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)

And asking hundreds of people to change how they report isn't a winning move.