Tom’s Tutorials For Excel: Calculating the Date for Easter

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!

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
13 comments on “Tom’s Tutorials For Excel: Calculating the Date for Easter
  1. Ian MacCrimmon says:

    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

    • Tom Urtis says:

      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.

      • Emil says:

        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)

  2. Zohar says:

    Thanks. Ive used it to calculate easter dates for easter to this website https://www.whenholiday.com/en-us/easter-sunday/

  3. Ridwaan says:

    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

  4. Arne says:

    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

    • Tom Urtis says:

      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

  5. sports gloves says:

    When I originally commented I clicked the
    “Notify me when new comments are added” checkbox and now each
    time a comment is added I get four emails with the same comment.
    Is there any way you can remove me from that service?
    Thanks!

  6. Harrison Wesch says:

    To the atlaspm.com owner, Good work!

Leave a Reply

Your email address will not be published. Required fields are marked *

*