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

[forest1959]

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

 

[mart37]

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:

=IF(TODAY()>=B2,NETWORKDAYS(B2,MIN(C2,TODAY())),0)

Future days:

=IF(C2>=TODAY(),NETWORKDAYS(MAX(B2,TODAY()),C2),0)

 

[forest1959]

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]

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:

=IF(TODAY()>=B2,NETWORKDAYS(B2,MIN(C2,TODAY())),0)

Future days:

=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!