Results 1 to 14 of 14
Thread: How to calculate Easter (All?)

20040329, 18:52 #1
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
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 19002078 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,)*76
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)7,30)*14%,)*76

20040329, 19:27 #2
 Join Date
 Jan 2001
 Location
 Quedgeley, Gloucester, England
 Posts
 5,333
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: How to calculate Easter (All?)
No doubt Easter was invented by the ubiquitous Dr John Stockton.
<font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>
Ita, esto, quidcumque...

20040329, 20:10 #3
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
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>

20040329, 20:16 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to calculate Easter (All?)
The first two get a #Value error on my XL2K.
Legare Coleman

20040329, 20:24 #5
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
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,)*76
Another possibilty is as this competition was held in Germany the formula could be dmy based rather than mdy

20040329, 20:49 #6
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
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 dmy & mdy 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.

20040330, 18:32 #7
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20040330, 19:22 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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,)*76</pre>
Dollar is just rounding the value to the nearest dollar. You could also use ROUND function.
Steve

20040330, 19:24 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to calculate Easter (All?)
<P ID="edit" class=small>(Edited by sdckapr on 30Mar04 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,)*76
Steve

20040330, 19:44 #10
 Join Date
 Feb 2003
 Location
 St Louis, Missouri, USA
 Posts
 254
 Thanks
 6
 Thanked 1 Time in 1 Post
Re: How to calculate Easter (All?)
Here is a userdefined function at http://www.barasch.com/excel/cEa21184.htm. What I want is the calculation for Jewish holidays.
Alan

20040330, 19:47 #11
 Join Date
 Apr 2003
 Location
 Morgantown, Pennsylvania, USA
 Posts
 94
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20040330, 20:03 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.)

20040330, 20:11 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: How to calculate Easter (All?)
Check out this site. It has routines and an addin
Steve

20040331, 00:38 #14
 Join Date
 Jan 2004
 Location
 Birmingham, West Midlands, United Kingdom
 Posts
 219
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to calculate Easter (All?)
There's a function =EASTERDATE, along with 40 other usefultosome functions in the excellent addin 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.