Calculating number of working days between dates - TRICKY!!

[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 working days, which are Tuesday, Wednesday, Friday and Saturday (so exclude Mondays, Thursdays, Sundays and bank holidays) and only those dates which fall within the last year. To explain:

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

Column A holds 'Date from'.
Column B holds 'Date to'.
Column C needs to count the number of working days between the two dates (correctly excluding non-working days and bank holidays.)
Column D should count the number of working days between the Col A and Col B dates, as long as the dates are within the past year.

It's columns C and D that I need assistance with please.

Examples:-

A5 contains Mon 4 May 09
B5 contains Wed 6 May 09
C5 should calculate as 2 - because there are two working days between those dates (the Tues and the Wed).
D5 should calculate as 0 - because this range of dates is over a year ago.

A6 contains Fri 19 June 09
B6 contains Tues 23 June 09
C6 should calculate as 3 - (the Fri, Sat and Tues).
D6 should calculate as 3 - because this range of dates is within the past year.

A7 contains Tue 2 June 09
B7 contains Sat 6 June 09
C7 calculates as 4 - (the Tues, Wed, Fri and Sat).
D7 should calculate as 2 - because the 5th & 6th June 09 are working days within the past year but the 2nd & 3rd June 09 are not.

Help!!
Thanks in advance.

p.s. I already have a range called 'Holidays' which lists all bank holidays.

 

[dave3009]

Perhaps

=NETWORKDAYS(StartDate, EndDate, Holidays)

 

[KezD]

Hi Dave3009

As I understand it, Networkdays doesn't count weekends (I need the Saturdays) and does count all weekdays (when I only want to count some of them!)

I think I'm going to have everyone scratching their heads with this one!

 

[Andrew Poulsom]

Try in C5:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A5&":"&B5)),2)={2,3,5,6})*(ISERROR(MATCH(ROW(INDIRECT(A5&":"&B5)),Holidays,FALSE))))

and in D5:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A5&":"&B5)),2)={2,3,5,6})*(ROW(INDIRECT(A5&":"&B5))>DATE(YEAR($A$1)-1,MONTH($A$1),DAY($A$1)))*(ISERROR(MATCH(ROW(INDIRECT(A5&":"&B5)),Holidays,FALSE))))

copied down. The formulas assume that today's date is in A1.

 

[KezD]

OMG Andrew - you've cracked it! And in record-breaking time!

Thank you so much for your help. You're a star!