Special dates in EXCEL

[luvstigers2003]

Hi gang....

Is there a way for EXCEL to tell me if a date is a stautory holiday (
Good Friday, Easter Monday, Christmas etc... ) ?

Basically I want some way to search a list of dates and have excel
tell me which dates are holidays.

 

[Tom Ogilvy]

You would need to have a list of your holidays on a worksheets. Assume this
is named Holiday

assume you have a list of dates to check in column A of another sheet with
the first date in A2

in B2 you could put

=if(iserror(match(A2,Holiday,0)),"","Holiday")

Drag fill this formula down the column.

If you wanted it in code

with Worksheets("Dates")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End with
for each cell in rng
res = Application.Match(cell.Value,Range("Holiday"),0)
if not iserror(res) then
cell.offset(0,1).Value = "Holiday"
end if
Next

 

[Guest]

Because Easter, Good Friday, etc. are dates not based strictly on rules like
"the 25 of December" or "the fourth Thursday in November" or "the first
Monday after the first Sunday in September", they must be determined manually.

You may create a list of dates and run code against that list, but you have
to come up with the list, Excel isn't that smart.

Dale Preuss

 

[Myrna Larson]

There IS a rule for calculating the date of Easter <g> and for Good Friday.

Easter is the first Sunday following the first full moon which occurs on or
after March 21.

Good Friday is the Easter date - 2.

Here's some code to calculate the date of Easter. YOu can find more "terse"
versions if you search Google news groups.

Function Easter(Yr As Long) As Date
' Date of Easter for any year
' Algorithm from Knuth, The Art of Computer Programming

Dim Century As Long
Dim Sunday As Long
Dim Epact As Long
Dim Golden As Long
Dim LeapDayCorrection As Long
Dim SynchWithMoon As Long
Dim N As Long

' [Golden Number in Metonic cycle]
Golden = (Yr Mod 19) + 1

' [Century]: when Yr is not a multiple of 100,
' Century is the century number
Century = Yr \ 100 + 1

' [Corrections]
' LeapDayCorrection is the number of century years that aren't leap years
LeapDayCorrection = 3 * Century \ 4 - 12

' SynchWithMoon is a special correction to synchronise
' Easter with the moon's orbit
SynchWithMoon = (8 * Century + 5) \ 25 - 5

' [Find Sunday]: March((-Sunday mod 7) will be a Sunday
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10

' [Epact]: specifies when a full moon occurs.
' If Epact = 25 and the golden number is greater than 11, or
' Epact = 24, then increase Epact by 1

Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1

' [Find full moon]
N = 44 - Epact
If N < 21 Then N = N + 30

' Easter is the first Sunday following the first full moon
' which occurs on or after March 21.
' The Nth of March is a calendar full moon.

' [Advance to Sunday]
N = N + 7 - ((Sunday + N) Mod 7)

'Easter is March N or April (N - 31)
Easter = DateSerial(Yr, 3, N)

End Function 'Easter

 

[Peter T]

A quick Google search finds various ways to get holidays for your country
from Outlook into Excel, here's one:

http://www.entourage.mvps.org/cross_platform/

then follow Tom's suggestion.

Regards,
Peter