Forum Discussion

Maride2225's avatar
Maride2225
Copper Contributor
Jul 13, 2021

Count days from date range

I have a list of time off requests that are listed as date ranges (ie. 6/7/21 - 6/10/21 ) and I need to make a count of how many days off are requested for each date, ideally without having to manual...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 13, 2021

Maride2225 

This only works in Excel 365.  Even then, it is something of a nightmare!

= LET(
   h, SEARCH("-", requestedDates),
   start,  --MID(requestedDates, 1, h-2),
   end,    --MID(requestedDates, h+2, 9),
   minStart, MIN(start),
   maxEnd,   MAX(end),
   calendar, SEQUENCE(1, maxEnd-minStart+1, minStart ),
   request, (calendar>=start)*(calendar<=end),
   count,    MMULT(TRANSPOSE(request), SIGN(start)),
   IF({1,0}, TRANSPOSE(calendar), count) )

The formula extracts the two parts of the date range and coerces each to become a date (must be the correct locale for the machine).  The 'calendar' is the sequence of dates inclusive of the first and last requested date.  Each date is tested against each leave request.  MMULT is used to sum over the requests.

Note: I had to rewrite the dates in a non-US format for the formula to work.

Resources