Excel Easter Date Calculations

eastercalc Easter has passed for this year, but it’s never too early to figure out when Easter will occur next year. Then, you can book your vacation for that date, and be out of town when the family shows up for Easter dinner!

Last week, Excel MVP Jerry Latham showed us how to calculate distance in Excel, based on starting and ending points.

Today, Jerry explains how you can calculate Easter dates in Excel, using a worksheet formula or Excel User Defined Function (UDF).

Easter Formulas

First, Jerry gives a few examples of Excel worksheet formulas for Easter dates, where the year is entered in cell A1. This formula, by Norbert Hetterich, works if the regional settings have a day/month/year date format.

=FLOOR(DAY(MINUTE(A1/38)/2+56)&”/5/”&A1,7)-34

And here’s another Easter formula, by Thomas Jansen:

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

However, the worksheet formulas can’t be used reliably to determine dates of Easter after 2203, and the Hetterich formula fails for 2079.

Easter User Defined Functions

If you’re working with a limited range of years, one of the worksheet functions might be all that you need. However, for accuracy in a wider range of years, you can use an Excel UDF instead.

Jerry’s article includes 4 Excel UDFs for calculating Easter dates in Excel, and he has tested all the UDFs for speed and accuracy.

To use the UDFs, copy the code to your workbook, then type the function name in a worksheet formula, e.g.

=CalculateEaster(A1)

Download the Easter Date Workbook

Visit the Excel Easter Date Calculation page on the Contextures website, where you can see the Easter formulas and UDFs, and copy them to your own workbook. Or, download Jerry’s sample file, and work with the code and formulas there.

If you have questions or comments, please let Jerry know. You can post a comment below, or send him an email at HelpFrom@JLathamSite.com

________________

You may also like...

12 Responses

  1. I once read that excel converts all variables defined as integer to long.
    Would it make a difference for the speed of the UDF’s if they were defined as long in the first place…?

    regards

    Jelle-Jeroen

  2. Rick Rothstein (MVP - Excel) says:

    Just to add to the mix, below is an Easter function that can be called from within one’s own VB code or used as a UDF (user defined function) on a worksheet. The maximum value for the YearIn argument is 9999. The minimum value for the YearIn argument is 1583 (an early date when the Gregorian Calendar came into being) when the function is called from other VB code, 1900 when called as a UDF with the 1900 Date System active and 1904 when the 1904 Date System is active (yes, the code works properly with the 1904 Date System). If out of bounds years are passed into the function, an “Invalid procedure call or argument” error is raised when called from other VB code and a #VALUE! error is generated when called as a UDF.

    Function Easter(ByVal YearIn As Integer) As Date
    
        Dim a As Long, b As Long, c As Long, d As Long
        Dim e As Long, f As Long, g As Long, h As Long
        Dim i As Long, k As Long, l As Long, m As Long
        Dim n As Long, p As Long, Adjustment1904 As Long
    
        If ActiveWorkbook.Date1904 Then Adjustment1904 = 1462
    
        If TypeName(Application.Caller) = “Range” Then
            If YearIn < 1900 – 4 * ActiveWorkbook.Date1904 Then
                Easter = CVErr(xlErrValue)
                Exit Function
            End If
        ElseIf YearIn < 1583 Then
            Err.Raise 5
        End If
    
        a = YearIn Mod 19
        b = YearIn  100
        c = YearIn Mod 100
        d = b  4
        e = b Mod 4
        f = (b + 8 )  25
        g = (b – f + 1)  3
        h = (19 * a + b – d – g + 15) Mod 30
        i = c  4
        k = c Mod 4
        l = (32 + 2 * e + 2 * i – h – k) Mod 7
        m = (a + 11 * h + 22 * l)  451
        n = (h + l – 7 * m + 114)  31
        p = (h + l – 7 * m + 114) Mod 31
    
        Easter = DateSerial(YearIn, n, p + 1) – Adjustment1904
    
    End Function
    

    I got the underlying algorithm from this website…

    http://thoughtproject.com/Snippets/Easter/index.htm

    and then modified it to work as an Excel UDF with either date system. I double checked several random year arguments at this site to verify the accuracy of the output…

    http://smart.net/~mmontes/ec-cal.html

  3. Rick Rothstein (MVP - Excel) says:

    Hmm… that smiley face in the code is supposed to be the number 8… I have no idea why it got turned into an emoticon.

  4. Thanks Rick, and I added a space character after the 8, to get rid of that smiley face. When someone copies your code into a module, it will automatically remove the space.

  5. Rick Rothstein (MVP - Excel) says:

    @Deb,

    You are quite welcome, of course. When I looked through the various referenced Easter functions, it did not seem they provided a 9999 top end and/or did not account for the 1904 Date System, so I decided to post the code that I did for anyone who might need both of those.

    Do you have any idea why the number 8 got coverted to an emoticon (I’m looking ahead for future code posting and how to recognize/avoid the problem)? Also thanks for spacing the code back out… it looks so much better that way. How is it you can do that and we can’t, even if we use code tags? Is there a different HTML tag that we can use do duplicate the space preservation that you were able to achieve?

  6. Rick, instead of the “code” tags, I use “pre” tags.

    WordPress has a default setting somewhere, that converts some text strings to graphic emoticons, so I’ll try to turn that off.

  7. Rick Rothstein (MVP - Excel) says:

    Hmm, “pre” is not one of the tags listed when we click the “You can use these HTML tags” link. I am going to test it out below with a nonsense code fragment just to see if we can use it or if it is something reserved only for the forum hostess.

    Sub TestingPreTag()
      If YouAreReadingThis Then
        YouCanIgnoreIt Because, Its, Meaningless
      End If
    End Sub
  8. Rick Rothstein (MVP - Excel) says:

    Obviously it didn’t work :-(

    When I look at the Source for this webpage, my attempt to use “pre” tags got changed to letter “p” tag instead (the re was removed), so I guess that tag is not globally available to responders.

  9. In WordPress admin, go to Settings – Writing and uncheck the “Convert emoticons…” box. That will fix it, but I won’t be able to properly convey my mood when commenting. >:P

  10. Thanks Dick, and sorry about the loss of the smiley faces. :-(

  11. @Rick, strange that I can use that “pre” code in the comments too, but it won’t allow you to do the same.
    Checking on the WordPress site, I found that only the site Admins and Editors can use special HTML in the comments.
    If you post a comment, and want the pre tags, just let me know, and I’ll add them.

  12. Frank Schneider says:

    Format length formula with year in A2
    1900 84 =FLOOR((8&-A2)-MOD(30*INT(11*MOD(A2;19)-68%*INT(A2%)+INT(A2%/4)+44,55);29,032);7)-97
    =7*INT((8&-A2)/7-MOD(4*INT(68%*INT(A2%)-INT(A2%/4)-11*MOD(A2;19)-43,55);4,13797))-97
    1904 83 =FLOOR((5&-A2)-MOD(30*INT(11*MOD(A2;19)-68%*INT(A2%)+INT(A2%/4)+44,55);29,032);7)-5
    =7*INT((5&-A2)/7-MOD(4*INT(68%*INT(A2%)-INT(A2%/4)-11*MOD(A2;19)-43,55);4,13797))-5
    190x 95 =FLOOR((5&-A2)-DAY(6)-MOD(30*INT(11*MOD(A2;19)-68%*INT(A2%)+INT(A2%/4)+44,55);29,032);7)+DAY(1)

Leave a Reply

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