Count the working days between two dates

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
31
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,612
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 14, 2016
Messages
3,643
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.
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,612
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,502
Messages
5,636,694
Members
416,935
Latest member
Atulcp

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
Top