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 [email protected]
________________
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
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.
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
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.
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.
@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?
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.
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.
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.
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
Thanks Dick, and sorry about the loss of the smiley faces. 🙁
@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.
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)