Forum Discussion

nav101's avatar
nav101
Copper Contributor
Jul 06, 2022

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

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

excel
Formulas and Functions

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 06, 2022

    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

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 06, 2022

    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)))
        ))
     )

Resources