Count the working days between two dates

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
37
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

Do you have any ideas on how I can do this? Please help!!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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)))
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top