[SOLVED] Count days within a date range that fall into selected months (via checkboxes)

Count days within a date range that fall into selected months (via checkboxes)

I’m working on an Excel solution where I have date ranges with a start date (D3) and an end date (E3) per row.The dates are formatted like this: dd.mm.yyyy. hh:mm but the hours and minutes are not really important to me as my data isnt that accurate. On another sheet (Sheet2), I have checkboxes for each month (from January to December in cells W9:W20). These checkboxes return either TRUE or FALSE, depending on whether the month is selected.

What I want to achieve:

I want to count only the days in the date range that fall into the months that are selected (checked). Example scenarios:

Start Date  End Date    Selected Month(s)   Expected Result
01.05.2024  01.05.2024        May                  1                      
03.05.2024  12.05.2024        May                 10
02.04.2024  06.05.2024        May                  6 
02.04.2024  08.06.2024        May                 31
02.04.2024  08.06.2024        May,June            39

What I’ve tried:

I used this formula to copy F3 which is a simple subtraction of the two dates, so it doesn’t handle multi-month ranges correctly — it just compares months by number and doesn’t calculate overlapping days accurately:

=IF(SUMPRODUCT(
  ('Faccns Übersicht'!$W$9:$W$20=TRUE) *
  (ROW($1:$12)>=MONTH(D3)) *
  (ROW($1:$12)<=MONTH(E3))
)>0, F3, "")

This fails when the date range spans multiple months and only some months are selected — I need only the days from the selected months, not the entire range. What I’m looking for:

An Excel formula that:

Takes a start and end date (D3, E3)
Checks which months (January to December) are selected using checkboxes on 'Sheet2'!W9:W20
Calculates the total number of days in the date range that fall within those selected months

Any help or formula ideas would be greatly appreciated!

Edit: If possible I would like to stick to basic excel functions and avoid using VBA/macros.

Solution

You can use this formula:

= LET(expandedMonths,MONTH(SEQUENCE([@EndDate]-[@StartDate]+1,,[@StartDate])),
    selected,FILTER(SEQUENCE(12),selectedMonths=TRUE),
    ROWS(FILTER(expandedMonths,ISNUMBER(MATCH(expandedMonths,selected,0)))))

lkjl

expandedMonths returns an array with all months for Start to end date - this is then filtered by the selected months from named range "Selected months" E2:E13