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 manually input each date between the date ranges. So i want to be able to input


6/7/21 - 6/10/21
6/8/21 - 6/8/21
6/8/21 - 6/9/21

and be able to get a list

6/7/21 - 1
6/8/21 - 3
6/9/21 - 2
6/10/21 1

excel
Formulas and Functions

3 Replies

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

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 13, 2021

    Maride2225  See the attached.

    first I assumed that the dates may be entered just as you said (or as a single date)

    second I assumed you have the latest version of Excel (365 subscription)

    So first I converted that mix of dates and text to 2 columns for start date and end date (columns C and D)

    then I created a list of all possible dates from first to last and the corresponding count that particular date is found in any of those ranges (columns F and G)

    then I filtered that table of all dates to show only the dates that have a count > 0  (columns I and J)

    THEN I converted all of those helper columns into a single massive LET statement in M2 (i.e. you could delete all the other columns but I left them there to show the process I did)

Resources