SOLVED

New Contributor

# Networkdays formula to calculate specific date range for start and end date

Dear All,

I am currently trying to figure out formula to calculate specific period of working days for each month excluding week end and holidays. For example

Start Date     End date

2022/3/1       2022/3/31    this I can calculate by workdays or networkdays formula.

2022/3/1       2022/4/15    To calculate working days for March only I use networkdays formula like =Networkdays(A"",eonmonth(A"",0),B"")  this formula help me calculate exact working days excluding weekend and holiday.

My main pain point is below scenario.

Start date : 2022/2/1 and End date 2022/4/15

By which formula I can calculate total working days only for March which exclude weekends and holiday?

Any help is highly appreciate.

Regarda,

6 Replies

# Re: Networkdays formula to calculate specific date range for start and end date

@Paddy2803 Perhaps the approach demonstrated in the attached file will help.

The picture shows the formula that uses named ranges. Cell E1 contains the date for the 1st of March, formatted to display the month name only.

# Re: Networkdays formula to calculate specific date range for start and end date

@Riny_van_Eekelen Hi Thanks for your response, just have 1 doubt the formula which you used has EOMONTH function but what if end date is for example 15th March and start date is 15 february 2022. How it can calculate only March working days in same formula which you shared earlier below I am sharing few cases for better understanding.

Start Date    End Date

2022/3/1      2022/3/31

2022/3/1      2022/3/20

2022/3/15    2022/3/31

2022/2/15    2022/3/15

2022/2/1      2022/4/15

With above cases I am looking to count actual working days for March excluding weekeds and Holiday

Appreciate if you can help.

# Re: Networkdays formula to calculate specific date range for start and end date

Try this one:

``=NETWORKDAYS(MAX(start,DATE(2022,MONTH(mth),1)),MIN(end,EOMONTH(DATE(2022,MONTH(mth),1),0)),holidays)``

# Re: Networkdays formula to calculate specific date range for start and end date

@Riny_van_Eekelen Thank you but not sure above formula is giving an error. Its not giving a expected result.
best response confirmed by Paddy2803 (New Contributor)
Solution

# Re: Networkdays formula to calculate specific date range for start and end date

@Paddy2803 Perhaps you can specify the error rather than just mention that there is "an error".

Edit: attached an updated file.

# Re: Networkdays formula to calculate specific date range for start and end date

@Riny_van_Eekelen Thanks a lot, Its working fine now. My bad that I couldnt get it earlier, Thank you for your support.