# Count the working days between two dates

#### Tatum2020

##### New Member
Hi

I have a very large number of events, by row, which have a start date, and an end date.

And I have a separate table with months as the headers (Jan-21, Feb-21, etc, through to Dec-22)

What I'm wanting Excel to return is the number of working days under each month, for each of the events, if they were "active" for even part of any of those months.

Example:

Start Date 01/01/2021, End Date 31/05/2021.

So, I want it to return 20 for January, 20 for February etc.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### jasonb75

##### Well-known Member
I'm sure that there is a simpler formula, but this is the most accurate method that I've found so far.
Book1(AutoRecovered)2.xlsm
ABCDEFGHIJKLMN
1Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
201/01/202131/05/202121202322210000000
Sheet3
Cell Formulas
RangeFormula
C2:N2C2=MAX(0,NETWORKDAYS(MEDIAN(\$A2,C\$1,EOMONTH(C\$1,0)),MEDIAN(\$B2,C\$1,EOMONTH(C\$1,0)))-OR(C\$1<EOMONTH(\$A2,-1)+1,EOMONTH(C\$1,0)>EOMONTH(\$B2,0)))

#### Tetra201

##### MrExcel MVP
Here is a shorter one:
Excel Formula:
``=MAX(0,NETWORKDAYS(MAX(\$A2,C\$1),MIN(\$B2,EOMONTH(C\$1,0)),Holidays))``
where Holidays is a named range that contains holiday dates.

#### jasonb75

##### Well-known Member
I had a feeling that I used 1 or 2 more functions that I needed

If you look at my recent posts you'll find another thread that I've responded too with a date based question, if you can come up with a short formula there that doesn't use dynamic array functions then I will be seriously impressed.

Replies
2
Views
200
Replies
1
Views
33
Replies
8
Views
174
Replies
5
Views
286
Replies
6
Views
40

1,129,795
Messages
5,638,366
Members
417,023
Latest member
Zimbo38

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back