Counting the number of dates that have passed in a date range

forest1959

New Member
Joined
Mar 16, 2010
Messages
26
Good Afternoon

Can anyone please point me in the right direction to solve the following?

I have an annual leave spreadsheet eg
Employers Name Leave Start Date Leave End Date
Paul 08/02/2021 15/02/2021

I can count the number of days the employee is off thanks to the networkdays formula, but is there away of counting how many days during this period that have passed and how many are in the future

So on the example above, if todays date was 10/02/2021, how can i count how many days have passed (including "today"), and how many are in the future?

Regards
Paul
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Maybe something like this:
=Today()-{Leave Start}+1-Networkdays({Leave Start},Today(),)
={Leave End}-Today()-Networkdays(Today(),{Leave End})
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,026
Office Version
  1. 365
Platform
  1. Windows
Let's say that Start Date is in cell B2 and End Date is in cell C2.
Then here are your two formula:

Passed days:
Excel Formula:
=IF(TODAY()>=B2,NETWORKDAYS(B2,MIN(C2,TODAY())),0)

Future days:
Excel Formula:
=IF(C2>=TODAY(),NETWORKDAYS(MAX(B2,TODAY()),C2),0)
 
Solution

forest1959

New Member
Joined
Mar 16, 2010
Messages
26
Let's say that Start Date is in cell B2 and End Date is in cell C2.
Then here are your two formula:

Passed days:
Excel Formula:
=IF(TODAY()>=B2,NETWORKDAYS(B2,MIN(C2,TODAY())),0)

Future days:
Excel Formula:
=IF(C2>=TODAY(),NETWORKDAYS(MAX(B2,TODAY()),C2),0)
Thank you for your response. this worked perfectly
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,026
Office Version
  1. 365
Platform
  1. Windows
You arre welcome.
Glad we were able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,259
Messages
5,641,186
Members
417,198
Latest member
reg000

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