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

 

[Oaktree]

FWIW, at http://www.cpearson.com/excel/holidays.htm#Easter Chip Pearson's site references has a solution submitted by Joost Verbeek of:

=FLOOR(DATE(B2,5,DAY(MINUTE(B2/38)/2+56)),7)-34

where B2 has the year (as a number, not a date).

 

[WaxonWaxov]

That method works about 95% of the time, but doesn't always because it doesn't include the period of the orbit of the Moon. (yes, the orbit of the Moon)

The above formula returns the wrong day for the following years: 2079, 2204, and others.

 

[Oaktree]

Interesting, I never noticed that.

Maybe

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

where A1 stores the year (as a number, not a date) from J-walk's http://spreadsheetpage.com/index.php/tip/calculating_easter/ is correct on all accounts?

 

[WaxonWaxov]

your two formulas produce, for the most part, the same results. They return different values from mine more often the farther out into the future you get. (I ran this to the year 3000) this has to be due to the whole orbit of the moon thing.

Basically the rule is that Easter is the first Sunday after the first full moon after the Spring Equinox. In the early days, people would look at the moon and stuff and say "Easter this year on on (april or march) __" and they didn't really care about what day it would be in 50 years. Then, about the same time leap year was invented, some people decided to figure out a method for calculating the date for any given year. This is all described at http://en.wikipedia.org/wiki/Computus

For extra craziness, the entire cycle repeats itself after 5,700,000 years.

long story short, my fomula, even though it's as long as elephant's johnson, is an accurate way of using the calculation method referenced in the OP.

cheers,

 

[silkfire]

FWIW, at http://www.cpearson.com/excel/holidays.htm#Easter Chip Pearson's site references has a solution submitted by Joost Verbeek of:

=FLOOR(DATE(B2,5,DAY(MINUTE(B2/38)/2+56)),7)-34

where B2 has the year (as a number, not a date).

I need to calculate easter in a certain year, but this (and the DOLLAR formula; btw why dollars?) just give me #VALUE error, help me out!!

The B2 is entered as "2010" (without quotes) in General format.

 

[silkfire]

*bump*

 

[barry houdini]

I don't know what DOLLAR formula you are referring to but the one you quoted works for me. If B2 has 2010 then the formula returns 4/4/2010 which is the date of Easter Sunday as expected.

Are you sure B2 has just a year?

 

[silkfire]

I don't know what DOLLAR formula you are referring to but the one you quoted works for me. If B2 has 2010 then the formula returns 4/4/2010 which is the date of Easter Sunday as expected.

Are you sure B2 has just a year?

Yes, B2 is just 2010. Anyway I read that this formula only works with US dates and my regional settings are Swedish, although my Excel 2007 is in English. I read that the DOLLAR formula should work regardless of date system but it gives me #VALUE anyhow.

I found a slightly remade FLOOR formula which "worked" but gave me the result 2 April for 2010 and and 21 April for 2010 (should be 4 April and 24 April instead). So what's wrong?

Btw when using DOLLAR() formula on a number I get for example "5 kr" as a result, my local currency SEK. Maybe that's the problem? Could you develop a formula for Swedish settings?

This is the dollar formula I meant:

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

 

[barry houdini]

This formula is universal, it doesn't depend on any date format

=FLOOR(DATE(B2,5,DAY(MINUTE(B2/38)/2+56)),7)-34

I tried changing to Swedish regional settings and it still worked for me except I had to change the commas to semi:colons

The DOLLAR version only works with US type date settings because the "4/"&A1 is supposed to give you 1st April in the relevant year.

If I use it with my normal date settings (UK) then it gets the wrong date but I can change it to this to work

=DOLLAR(("1/4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

DOLLAR function is only really used here for rounding and it should work regardless of your currency, you could use ROUND instead, i.e. this also works for me

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