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

#### forest1959

##### New Member
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### mart37

##### Well-known Member
Maybe something like this:
=Today()-{Leave Start}+1-Networkdays({Leave Start},Today(),)
={Leave End}-Today()-Networkdays(Today(),{Leave End})

#### Joe4

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)``

#### forest1959

##### New Member
Maybe something like this:
=Today()-{Leave Start}+1-Networkdays({Leave Start},Today(),)
={Leave End}-Today()-Networkdays(Today(),{Leave End})
Thank you for your response, this didn't seem to work

#### forest1959

##### New Member
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

You arre welcome.
Glad we were able to help!

Replies
5
Views
89
Replies
4
Views
363
Replies
5
Views
78
Replies
5
Views
291
Replies
2
Views
185

1,130,238
Messages
5,641,029
Members
417,189
Latest member
koelleyath

### 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