Calculate the Networkdays between two dates - as long as they occur within the past year

[KezD]

Hi

I'm trying to put together a sickness calculator that works on a rolling year basis.

I need to count the number of days between two dates, but only count the weekdays (exclude weekends and bank holidays) and only those dates which fall within the last year. To explain:

Let's say that today's date is 4 February 2010.

Column A holds 'Date from'.
Column B holds 'Date to'.
Column C counts the number of working weekdays between the two dates using the Networkdays function. (It correctly excludes weekends and bank holidays.)
Column D should count the number of working weekdays between those the Col A and Col B dates, as long as the dates are within the past year.

It's column D that I need assistance with.

Examples:-

A5 contains Wed 14 Jan 09
B5 contains Sun 18 Jan 09
C5 calculates as 3 (which is correct)
D5 should calculate as 0 - because this range of dates is over a year ago.

A6 contains Thur 18 June 09
B6 contains Fri 19 June 09
C6 calculates as 2
D6 should calculate as 2 - because this range of dates is within the past year.

A7 contains Tue 3 Feb 09
B7 contains Fri 6 Feb 09
C7 calculates as 4
D7 should calculate as 3 - because the 4th, 5th & 6th Feb 09 are within the past year but the 3rd Feb 09 is not.

Help!!

Thanks in advance.

 

[pgc01]

Hi KezD
Welcome to the board

Assuming Holidays is a named range with your holidays, try:

=MAX(0,NETWORKDAYS(MAX(A5,DATE(YEAR(TODAY())-1,MONTH(TODAY()), DAY(TODAY()))),B5,Holidays))

 

[KezD]

You're fab!

Thank you so much - that works a treat!