How to calculate the date of Easter...

[WaxonWaxov]

Hello,

This isn't a question, but a share. This formula I just finished will caluclate the date of (Roman Catholic/Protestant) Easter in the year of a given date and it does it with no VBA.

It uses the Meeus/Jones/Butcher Gregorian algorithm. This algorithm for calculating the date of Easter Sunday is given by Jean Meeus in his book '"Astronomical Algorithms" (1991) which in turn cites Spencer Jones in his book "General 'Astronomy" (1922) and also the Journal of the British Astronomical Association (1977).

This algorithm also appears in The Old Farmer's Almanac (1977), p. 69.
The JBAA cites "Butcher's 'Ecclesiastical Calendar" (1876).

The method is valid for all Gregorian years and has no exceptions and requires no tables.

As described on Wikipedia at http://en.wikipedia.org/wiki/Computus

This example assumes the given date is in cell A1. Note that it gives the date within the year. So if a date in October is given, it calculates the date of Easter the preceeding spring. If a date in February is given, it gives the date of Easter that following spring.

Note that if your business observes Good Friday, you would simply need to subtract two days from the resultant date. For 'Easter Monday' add one day, etc. (Ash Wednesday is the resultant date minus 39 and Mardi Gras is the resultant date minus 40)

=DATE(YEAR(A1),MONTH(DATE(YEAR(A1),MONTH(1),DAY(1)))+((INT(((MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))+(MOD((32+2*(MOD(INT(YEAR(A1)/100),4))+2*(INT((MOD(YEAR(A1),100))/4))-(MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))-(MOD((MOD(YEAR(A1),100)),4))),7))-7*(INT(((MOD(YEAR(A1),19))+11*(MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))+22*(MOD((32+2*(MOD(INT(YEAR(A1)/100),4))+2*(INT((MOD(YEAR(A1),100))/4))-(MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))-(MOD((MOD(YEAR(A1),100)),4))),7)))/451))+114)/31))-1),DAY(DATE(YEAR(A1),MONTH(1),DAY(1)))+(((MOD(((MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))+(MOD((32+2*(MOD(INT(YEAR(A1)/100),4))+2*(INT((MOD(YEAR(A1),100))/4))-(MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))-(MOD((MOD(YEAR(A1),100)),4))),7))-7*(INT(((MOD(YEAR(A1),19))+11*(MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))+22*(MOD((32+2*(MOD(INT(YEAR(A1)/100),4))+2*(INT((MOD(YEAR(A1),100))/4))-(MOD((19*(MOD(YEAR(A1),19))+(INT(YEAR(A1)/100))-(INT(INT(YEAR(A1)/100)/4))-(INT(((INT(YEAR(A1)/100))-(INT(((INT(YEAR(A1)/100))+8)/25))+1)/3))+15),30))-(MOD((MOD(YEAR(A1),100)),4))),7)))/451))+114),31))+1)-1))

Enjoy

 

[mcinnes01]

I use excel 2003 and I want to use the first formula mentioned on this post, i've tried replacing parts of the formular by taking sections and referencing that cell instead, but i can't seem to break it down any more. Can any one advise me how I can use this particular formular in excel 2003?

Cheers,

Andy

 

[VBAMePlease]

Does Ash Wednesday always fall a flat 39-days before Easter? Any discrepancies I might encounter in doing so?

 

[bdr200]

Hi, I have created a table of available working days per calendar month allowing for weekends and public holidays in the UK. Not a difficult task apart from the dates for Easter which has been solved via the formula I gratefully copied from this thread. I used the formula

=ROUND(("4/"&[@Year])/7+MOD(19*MOD([@Year],19)-7,30)*0.14,)*7-6

to calculate Easter Sunday then Good Friday and Easter Monday are simply -2 and +1 respectively. The =MONTH([@[Good Friday]]) and =MONTH([@[Easter Monday]]) functions then indicates if these two respective dates are in March or April. The other public holidays in the UK always occur within known months so as all I needed was a day count within each month I did not need to calculate the actual dates.

Another table contains 1 row for each year and month in the range Jan 2015 to Dec 2050 (arbitrary finish month which is far enough ahead for many purposes). The 1st of the month is known, the last date of the month is derived via the function

=EOMONTH([@[Month Start]],0) where [Month Start] is the date of the 1st of the month

then the number of working days is returned by

=NETWORKDAYS.INTL([@[Month Start]],[@[Month End]],1)

The public holiday count by month is then a static value of 0, 1, or 2 for all months apart from MARCH and APRIL which use

=COUNTIFS(tblEasterDates[Year],[@Year],tblEasterDates[GF Month],3)+COUNTIFS(tblEasterDates[Year],[@Year],tblEasterDates[EM Month],3)

and

=COUNTIFS(tblEasterDates[Year],[@Year],tblEasterDates[GF Month],4)+COUNTIFS(tblEasterDates[Year],[@Year],tblEasterDates[EM Month],4)

to read the respective day counts in each month from the first table that contains the Easter dates. Finally 'Net Working Days' - 'Public Holidays' provides the required available days per calendar month.

I am happy to share the sheet containing the two tables but I cannot find a link to upload the file to this site so if anyone would like a copy please let me know. As a challenge I suspect the two tables I used could be combined into a single table.

Be aware that I have only spot checked some rows within the available days table so please test it for yourself before relying on it.

Thanks for providing the function for Easter Sunday which has enabled me to produce the required lookup table.

 

[Danton]

For the orthodox use this formula:
=DATE(A1;ROUNDDOWN((MOD(19*MOD(A1;19)+15;30)+MOD(2*MOD(A1;4)+4*MOD(A1;7)-MOD(19*MOD(A1;19)+15;30)+34;7)+114)/31;0);MOD((MOD(19*MOD(A1;19)+15;30)+MOD(2*MOD(A1;4)+4*MOD(A1;7)-MOD(19*MOD(A1;19)+15;30)+34;7)+114);31)+1)+13
This is valid since 1900 till 2100. After that use +14 at the end of the formula. You can check this link https://en.wikipedia.org/wiki/Conversion_between_Julian_and_Gregorian_calendars

 

[DRSteele]

No. Ash Wednesday is exactly 46 days before Easter Sunday.

This link seems to have a simple formula that works pretty well to calculate Easter Sunday.

=ROUND(DATE(A1,4,1)/7+MOD(19*MOD(A1,19)-7,30)*14%,0)*7-6

http://www.contextures.com/exceleastercalculation.html<strike></strike>

 

[Downes51]

Is there a formula for calculating the Easter Day in the Orthodox Church?

 

[fulgur]

Eternal Easter calculations

I also like formula:

=ROUND(DATE(A1,3,24)/7+MOD(19*MOD(A1,19)-7,30)*14%,0)*7+1

which works correctly from 1900 till 2199 and thanks to usage of universal DATE function does not depend on U.S. data formatting in Excel.
Unfortunately starting 2200 there is one day error in Full Moon calculation and therefore occasional errors in Easter (= 1st Sunday after Full Moon) calculation.

Therefore I'd like to post here following eternal formulas:

Gregorian Easter Sunday:

=ROUND((DATE(IF(A1<1900,8000,0)+A1,3,24)+MOD(19*MOD(A1,19)+INT((3*INT(A1/100)+3)/4)-INT((8*INT(A1/100)+12)/25)+15,30)*IF(MOD(A1,19)<11,56/57,54/55))/7,0)*7+1

Orthodox Easter Sunday:

=ROUND((DATE(IF(A1<1900,8000,0)+A1,3,24)+INT((3*INT(A1/100)+3)/4)-2+MOD(19*MOD(A1,19)+15,30))/7,0)*7+1

These formulas can be user not only through whole "Excel era" (from 1900 till 9999) but also for historic Easter calculations in previous centuries since a very early Year after the Gregorian calendar reform in October 1582 (i.e. from 1583 to 1899) if you omit Year in your data display format.

 

[AdiungoComplicatio]

That Gregorian Easter Sunday formula is good - it does seem to give the right answer well past the point where other ones fail.

The (more complicated) formula below gives the same results after 1900 - giving the correct date through the whole Excel era, but there is an explantion on this website that runs through every aspect of the formula, and ties it back to the official ecclesiastical calculation:

=DATE(A1,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(A1,19)>=11,2,1),
MOD(15+INT(A1/100)-INT(INT(A1/100)/4)-INT((13+8*INT(A1/100))/25)-11*MOD(A1,19),30)
+1)+8-WEEKDAY(
DATE(A1,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(A1,19)>=11,2,1),
MOD(15+INT(A1/100)-INT(INT(A1/100)/4)-INT((13+8*INT(A1/100))/25)-11*MOD(A1,19),30)
+1),1)