Days Calculation Between Dates by Month where start date to end date spans several months

Occasional Visitor

I'm trying to work out the number of working days between two dates but split out to show how many days in each month. Have many variations to work through

Start Date      End Date    Work days Duration     Mth1   Mth2   Mth3

1/6/22            15/7/23       30                                9          21

2 Replies

@nav101  in the attached I used this formula to do what you want.  You may want to customize weekends and holidays in the NETWORKDAYS.INTL() function:

=LET(start,A2,end,B2,
     monthCount,12*(YEAR(end)-YEAR(start))+MONTH(end)-MONTH(start)+1,
     s,SEQUENCE(,monthCount,0),
     BYCOL(s,LAMBDA(i,
        NETWORKDAYS.INTL( MAX(start,EOMONTH(start,i-1)+1),
                          MIN(end,EOMONTH(start,i)))
        ))
     )

@nav101 

If you are on pre-DA Excel (not 365 and not 2021) it could be

=MAX( 0, NETWORKDAYS.INTL( MAX( $A3, EOMONTH($A3, COLUMN() - COLUMN($A3) - 4) +1), MIN( $B3, EOMONTH($A3,  COLUMN() - COLUMN($A3) - 3   ) ) ) )

and drag it to the right