MirageSF
asked on
Work out when special days fall - such as Easter etc
Hi,
Whats the easiest way to calculate the date of each of the following for any given year...
New Year's Day
Good Friday
Easter Bank Holiday
May Bank Holiday
Spring Bank Holiday
Summer Bank Holiday
Christmas Day
Boxing Day
Obviously I know some like Christmas be the 25/12, boxing day, 26/12 etc, but Good Friday, Easter Monday etc harder to identify...
Whats the easiest way to calculate the date of each of the following for any given year...
New Year's Day
Good Friday
Easter Bank Holiday
May Bank Holiday
Spring Bank Holiday
Summer Bank Holiday
Christmas Day
Boxing Day
Obviously I know some like Christmas be the 25/12, boxing day, 26/12 etc, but Good Friday, Easter Monday etc harder to identify...
ASKER
Wow, love the answer... However...
=FLOOR(DATE(YEAR(A2),5,DAY (MINUTE(YE AR(A2)/38) /2+56)),7) -36 (WITH A2 BEING THE CELL CONTAINING THE YEAR)
This seems to find Good Friday, then add 3 days to get Easter Monday. Seems to work.
=FLOOR(DATE(YEAR(A2),5,DAY
This seems to find Good Friday, then add 3 days to get Easter Monday. Seems to work.
That has been done for you in various ways by many people. This method works for us:
Public Function DateEasterSunday(ByVal intYear As Integer) As Date
' Easter Sunday date calculation for years between 1583 and 4099.
' ===============================================================
' Original BASIC source:
' Ronald W. Mallen at rwmallen@chariot.net.au
' Astronomical Society of South Australia, Inc.
' https://www.assa.org.au/edm
' ===============================================================
' Adopted for VBA by Gustav Brock.
' 2012-10-11. Cactus Data ApS, CPH.
' ===============================================================
' Returns the date of Easter Sunday for the year intYear where
' intYear is a 4 digit year between 1583 and 4099.
' Will not fail for years outside the range, but the calculated
' dates will be unreliable.
' ===============================================================
' Easter Sunday is the Sunday following the Paschal Full Moon
' (PFM) date for the year.
' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book.
' The detailed method can be found using the link above.
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct.
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
' The \ operator may be unfamiliar - it means integer division
' for example, 30 \ 7 = 4 (the remainder is ignored).
' It's free! Please do not modify code or comments!
' ===============================================================
Const cintMonth As Integer = 3
Dim intDay As Integer
Dim intCentury As Integer
Dim intRemain19 As Integer
' Stepwise results from table A to E. These and their use
' are described and explained at the source.
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
Dim intD As Integer
Dim intE As Integer
' For intermediate results.
Dim intTemp As Integer
' Main components.
' First 2 digits of the year.
intCentury = intYear \ 100
' The remainder of year / 19.
intRemain19 = intYear Mod 19
' Calculate PFM date.
intTemp = (intCentury - 15) \ 2 + 202 - 11 * intRemain19
Select Case intCentury
Case 21, 24, 25, 27 To 32, 34, 35, 38
intTemp = intTemp - 1
Case 33, 36, 37, 39, 40
intTemp = intTemp - 2
End Select
intTemp = intTemp Mod 30
' Table A.
intA = intTemp + 21
' Compensate for leap year.
If (intTemp = 28 And intRemain19 > 10) Or intTemp = 29 Then
intA = intA - 1
End If
' Table B.
' Find the next Sunday.
intB = (intA - 19) Mod 7
' Table C.
intC = (40 - intCentury) Mod 4
If intC = 3 Then intC = intC + 1
If intC > 1 Then intC = intC + 1
' Table D.
intTemp = intYear Mod 100
intD = (intTemp + intTemp \ 4) Mod 7
' Table E.
intE = ((20 - intB - intC - intD) Mod 7) + 1
' Calculate the day.
' intDay is the days from ultimo February.
intDay = intA + intE
' Return the date of Easter Sunday.
DateEasterSunday = DateSerial(intYear, cintMonth, intDay)
End Function
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is also this website that list all the Bank Holidays through to 2029:
Future Bank Holidays
Not the easiest of formats for downloading but shouldn't be too onerous.
Future Bank Holidays
Not the easiest of formats for downloading but shouldn't be too onerous.
The Council of Nicaea established that Easter is the first Sunday after the first full moon occurring on or after the vernal equinox.
So: Find the vernal equinox. Then find the first full moon occurring on or after that date. Then find the first Sunday after that. Good luck.
But. The date varies around the world. Some churches still observe Easter under the Julian calendar, so in some countries it is an entirely different date.
in the United Kingdom, the Easter Act 1928 fixed Easter as the first Sunday after the second Saturday in April. However, this law was not implemented, although it remains on the UK Statute Law Database.
https://www.timeanddate.com/calendar/determining-easter-date.html
Easiest thing to do is generate a table of constants with the problematic dates for the next 50 years, by which time the software in question will be obsolete and long decommissioned.