# Thread: How to calculate Easter (All?)

1. ## How to calculate Easter (All?)

Have you ever wanted to use Excel to calculate what date Easter Sunday falls on but found that it does not include a built in way to do it?

The following 3 formulas all calculate the date for Easter Sunday for the years 1900-2078 and were the result of a competition held in 1999 by Hans Herber.

How do they work? I have no idea - anyone out there know?

The year is entered in cell A1
=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34
=DOLLAR((DAY(MINUTE(A1/38)/2+55)&"/4/"&A1)/7,)*7-6
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

2. ## Re: How to calculate Easter (All?)

No doubt Easter was invented by the ubiquitous Dr John Stockton.

3. ## Re: How to calculate Easter (All?)

Thanks John

If only all algorithms were that simple <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. ## Re: How to calculate Easter (All?)

The first two get a #Value error on my XL2K.

5. ## Re: How to calculate Easter (All?)

They assume the date seperator is a /

Try the following variants with seperator changed to a period.

=FLOOR(DAY(MINUTE(A1/38)/2+56)&".5."&A1,7)-34
=DOLLAR((DAY(MINUTE(A1/38)/2+55)&".4."&A1)/7,)*7-6

Another possibilty is as this competition was held in Germany the formula could be d-m-y based rather than m-d-y

6. ## Re: How to calculate Easter (All?)

Thanks Steve

I have just experimented with various regional settings and though the third formula worked with most settings that I tried it still failed for some. Some failed due to d-m-y & m-d-y issues, others failed due to the date separator (and some did not like both!). An example of how international settings can cause problems with Excel.

7. ## Re: How to calculate Easter (All?)

I hate to be dense, but I don't understand what the Dollar function is doing. I get the correct date from the Floor function. What are the next two doing? i get #Value for those.
I used Steve's variation: =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
The others didn't work for me.

But this is great thanks for providing.

8. ## Re: How to calculate Easter (All?)

Try this, I made a mistake when I moved the month in my original post

<pre>=DOLLAR(("4/"&DAY(MINUTE(A1/38)/2+55)&"/"&A1)/7,)*7-6</pre>

Dollar is just rounding the value to the nearest dollar. You could also use ROUND function.

Steve

9. ## Re: How to calculate Easter (All?)

<P ID="edit" class=small>(Edited by sdckapr on 30-Mar-04 14:24. Corrected error in 2nd formula)</P>They assume d/m/y. try these:
=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
=DOLLAR(("4/"&DAY(MINUTE(A1/38)/2+55)&"/"&A1)/7,)*7-6

Steve

10. ## Re: How to calculate Easter (All?)

Here is a user-defined function at http://www.barasch.com/excel/cEa21184.htm. What I want is the calculation for Jewish holidays.

11. ## Re: How to calculate Easter (All?)

Hello,

Well, I must be missing something. All of these formulas refer to cell A1. What's in A1, and in which cell are these formulas supposed to go?

12. ## Re: How to calculate Easter (All?)

Cell A1 should contain the year for which you want to determine the date of Easter.
The formula could go into any other cell on the same worksheet (you need only one of the formulas, not all of them.)

13. ## Re: How to calculate Easter (All?)

Check out this site. It has routines and an addin

Steve

14. ## Re: How to calculate Easter (All?)

There's a function =EASTERDATE, along with 40 other useful-to-some functions in the excellent add-in below. The help file claims it's ok up to the year 9999, I find it only works to 2078. Perhaps the SP3 service pack to xl2002 has 'improved' excel's performance?
http://longre.free.fr/english/
I got it for the =NUMTEXT formula, which reads a numeric value in a cell and writes it in English words, with a currency word if you want.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•