Workday Formula

[Gos-C]

Hello,

I am trying to create a holiday table that can be used for any year with the dates entered in column C. Column B contains formulas (as indicated below) to show the workday holidays. The formula in B10 works for the year 2005 but if I changed the year to 2004 it does not give the expected result -- i.e., Christmas Day s/b 27-Dec-04 and Boxing Day s/b 28-Dec-04. Any help?

A B C D
1 Holiday Date (Workday) Date Weekday
2 New Years Day 03-Jan-05 01-Jan-05 Saturday
3 Good Friday 25-Mar-05 25-Mar-05 Friday
4 Victoria Day 23-May-05 23-May-05 Monday
5 Canada Day 01-Jul-05 01-Jul-05 Friday
6 Civic Day 01-Aug-05 01-Aug-05 Monday
7 Labour Day 05-Sep-05 05-Sep-05 Monday
8 Thankgiving Day 10-Oct-05 10-Oct-05 Monday
9 Christmas Day 26-Dec-05 25-Dec-05 Sunday
10 Boxing Day 27-Dec-05 26-Dec-05 Monday

A
1 Holiday
2 New Years Day
3 Good Friday
4 Victoria Day
5 Canada Day
6 Civic Day
7 Labour Day
8 Thankgiving Day
9 Christmas Day
10 Boxing Day

B
1 Date (Workday)
2 03-Jan-05
3 25-Mar-05
4 23-May-05
5 01-Jul-05
6 01-Aug-05
7 05-Sep-05
8 10-Oct-05
9 26-Dec-05
10 27-Dec-05

C
1 Date
2 01-Jan-05
3 25-Mar-05
4 23-May-05
5 01-Jul-05
6 01-Aug-05
7 05-Sep-05
8 10-Oct-05
9 25-Dec-05
10 26-Dec-05

D
1 Weekday
2 Saturday
3 Friday
4 Monday
5 Friday
6 Monday
7 Monday
8 Monday
9 Sunday
10 Monday

The formula in B2 copied down to B9 is:
=IF(OR(WEEKDAY(D2)=1,WEEKDAY(D2)=7),WORKDAY((C2),1),C2)

The formula in B10 is:
=IF(OR(B9=C10,OR(WEEKDAY(D10)=1,WEEKDAY(D10)=7)),WORKDAY((C10),1),C10)

The formula in D2 copied down to D10 is:
=Weekday(C2)

Thanks,
Gos-C

[Skypilot]

Gos-C - I have done up a perpetual public holiday finder which is what you are after I think- Take a look at Public holidays.zip (For the ACT in Australia) attached (I hope) and add/change formulae to meet your needs.

[Gos-C]

Great! Thank you, skypilot. It's exactly what I wanted. (Surely, it's a small world.)

Gos-C

[Gos-C]

Gos-C - I have done up a perpetual public holiday finder which is what you are after I think- Take a look at Public holidays.zip (For the ACT in Australia) attached (I hope) and add/change formulae to meet your needs.

Hi Skypilot,

You never picked up my reply to your private message in Feb 2005. Please send me the explanation for the formulas as offerred.

Thanks,
Gos-C