How to calculate Easter (All?)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Thanks John

If only all algorithms were that simple

Reply | Quote

Thanks John

If only all algorithms were that simple

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

(Edited by sdckapr on 30-Mar-04 14:24. Corrected error in 2nd formula)

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

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

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

Steve

Reply | Quote

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

Reply | Quote

Check out this site. It has routines and an addin

Steve

Reply | Quote

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/%5B/url%5D
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.

Reply | Quote

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/%5B/url%5D
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.

Reply | Quote

Check out this site. It has routines and an addin

Steve

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

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

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

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

(Edited by sdckapr on 30-Mar-04 14:24. Corrected error in 2nd formula)

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

Reply | Quote

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

Reply | Quote