Tom’s Tutorials For Excel: Calculating the Date for Easter
Easter (often referred to as Easter Sunday) is a Christian festival and holiday. Easter always falls on a Sunday, but its date varies depending on the calendar year and the phase of the moon.
Specifically, Easter falls on the first Sunday after the full moon following the northern hemisphere’s vernal equinox. In Western Christianity, Easter can fall as early as March 22 or as late as April 25.
Excel is the best spreadsheet application under the moon, sun, and stars, but it is not an astrological observation platform. However, Easter’s dates can still be returned with creative formulas, such as the one in the picture that lists the decade’s years in range A4:A13.
To calculate dates for Easter, the formula in cell B4 copied down to cell B13 is:
=FLOOR("5/"&DAY(MINUTE(A4/38)/2+56)&"/"&A4,7)-34
I did not create this formula, and if I knew who did I’d love to credit that person!
Not even sure how the formula works, but it does, and here it is!
When i use this easter formula,
=FLOOR(“5/”&DAY(MINUTE(A4/38)/2+56)&”/”&A4,7)-34 ,
it always returns a #VALUE!
.
What am I missing?
Thanks
You weren’t missing anything. Actually, the formula is correct but when it hits the web page depending on your browser, some characters can be changed and that makes a difference. I’ve seen this with other formulas and VBA code, and it’s annoying.
In this case, the double quote characters were altered to be a different ascii character. I just opened a new workbook and in cell A4 I entered 2016 which is the current year. In another cell I entered
=FLOOR(“5/”&DAY(MINUTE(A4/38)/2+56)&”/”&A4,7)-34
which returned 42456 which is the correct answer because that is the serial number (seen by formatting the cell as a recognizable date) for Sunday, March 27, 2016. The difference was the double quotes that I re-entered when I edited the formula after copying it from the web page.
I can into a similar issue. From evaluating the formula I realised the problem arose from the date difference between the US and other countries. If the system is not set up for US date system use
=FLOOR(DAY(MINUTE(A4/38)/2+56)&”/”&“5/”&A4,7)-34
(and ; instead of , dependent on your system)
Brilliant! Thank you so much
Thanks. Ive used it to calculate easter dates for easter to this website https://www.whenholiday.com/en-us/easter-sunday/
Thank you, Zohar!
Thanks for the share.
“I did not create this formula, and if I knew who did I’d love to credit that person!”
This is a modification by Gerhardt Somitsch of Norbert Hetterich’s formula.
https://www.contextures.com/exceleastercalculation.html
Thank you!!
Norbert Hetterich’s formula won the competition for shortest Excel formula for finding Easter Sunday in the years 1900-2078. A slightly longer, but more understandable and language independent version would be =FLOOR(DATE(A4,5,DAY(MINUTE(A4/38)/2+56)),7)-34
Thanks, Arne. I would not have come up with either of these formulas, or others I have seen over the years.
I also saw this one from an unknown creator:
=DOLLAR((“4/”&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6
And for the VBA’ers out there, this UDF which I also did not compose:
Public Function EasterDate(Yr As Integer) As Date
Dim d As Integer
d = (((255 – 11 * (Yr Mod 19)) – 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 – ((Yr + Yr 4 + d + (d > 48) + 1) Mod 7)
End Function