Link to home
Start Free TrialLog in
Avatar of MirageSF
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...
Avatar of Dr. Klahn
Dr. Klahn

Easter is impossible to compute in a straightforward manner, having tried to do it once in the past.

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.
Avatar of MirageSF

ASKER

Wow, love the answer... However...

=FLOOR(DATE(YEAR(A2),5,DAY(MINUTE(YEAR(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.
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

Open in new window

/gustav
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.