Excel Easter Date Calculations

12 thoughts on “Excel Easter Date Calculations”

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

   Reply

2. 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

   Reply

3. 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.

   Reply

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.

   Reply

5. @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?

   Reply

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.

   Reply

7. 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

   Reply

8. 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.

   Reply

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

   Reply

10. Thanks Dick, and sorry about the loss of the smiley faces. 🙁

    Reply

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.

    Reply

12. 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)

    Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.