Jul 06 2022 04:58 AM
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
Jul 06 2022 06:29 AM
@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)))
))
)
Jul 06 2022 07:14 AM
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