Easterdate

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

Can someone please give me a formula to run the below.

EASTERDATE date of Easter Sunday for a given year

Thankyou.
 
Hi

A couple of years ago I saved a posting from Andreas Kleinert
(microsoft.public.de.excel) with a lot of formulas for various dates. For
Easter Sunday the formula will be (with year in cell A1)
=ROUND((DAY(MINUTE($A$1/38)/2+55)&".4."&$A$1)/7,)*7-6 +1
 
Hello Arvi from Steved
I am getting #Value!

I put the year 2004 and formatted it General and yyyy

What do I need to do to get it work

Cheers.
 
Hi Steved!

With the four digit year number in A1 and with non-US date settings:

=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34

With the four digit year number in A1 and with US date settings:

=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34

Please don't ask for an explanation! However Easter is the first
Sunday after the first full moon after the vernal equinox. Or more
precisely: Easter Sunday is the first Sunday after the "official''
full moon on or after the "official'' vernal equinox. The official
vernal equinox is always 21 March. The official full moon may differ
from the real full moon by one or two days.

You'll find a pretty comprehensive discussion of this and other
calendar topics at:

http://www.tondering.dk/claus/cal/
 
Hi

For me it works (returns 12.04.2004).
There can be problems with Excel interpreting date strings, depending on
international settings for Windows - probably this is the case. Edit the
formula so that 'DAY(MINUTE($A$1/38)/2+55)&".4."&$A$1' part does return a
date string recognized by your Excel (you can test it, entering p.e.
12.04.2004 into some cell, and setting cells format to general - when the
entry changes to number, then the datestring is recognized as date), or
change the formula to more general form:

=ROUND(DATE($A$1,4,DAY(MINUTE($A$1/38)/2+55))/7,)*7-6 +1
 
Thankyou Norman

I put 2004 in A1 and it returned 11/Apr/2004
which is correct. I Have a created a Calendar
which I have formated 2004 "yyyy" is their away
do you think I could use it using yyyy

Thankyou.
 
Hi Steved!

I think you're saying that you have a date formatted yyyy to show the
year number 2004.

The underlying value is the date serial number for the date and that
will the value returned by a reference to that cell.

If you want to use that cell as your reference then instead of A1 in
the formulas given by me and others use YEAR(A1).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
 
Oops!

This formula was for Easter Monday - remove 1 from formula!

=ROUND(DATE($A$1,4,DAY(MINUTE($A$1/38)/2+55))/7,)*7-6

Arvi Laanemets
 
Thankyou Norman
-----Original Message-----
Hi Steved!

I think you're saying that you have a date formatted yyyy to show the
year number 2004.

The underlying value is the date serial number for the date and that
will the value returned by a reference to that cell.

If you want to use that cell as your reference then instead of A1 in
the formulas given by me and others use YEAR(A1).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)


.
Click to expand...
 
Hi Steved!

A pleasure.

Before you ask <vbg>

Add 39 to Easter Sunday formula to get Ascension Day (a Thursday).
 
Thankyou
-----Original Message-----
Hi

For me it works (returns 12.04.2004).
There can be problems with Excel interpreting date strings, depending on
international settings for Windows - probably this is the case. Edit the
formula so that 'DAY(MINUTE($A$1/38)/2+55)&".4."&$A$1' part does return a
date string recognized by your Excel (you can test it, entering p.e.
12.04.2004 into some cell, and setting cells format to general - when the
entry changes to number, then the datestring is recognized as date), or
change the formula to more general form:

=ROUND(DATE($A$1,4,DAY(MINUTE($A$1/38)/2+55))/7,)*7-6 +1


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)






.
Click to expand...
 
With the four digit year number in A1 and with US date settings:
=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
Click to expand...

Hi. I don't have an answer, but just an observation. For the Year 2079,
this function returns "Sunday, April 16, 2079." The program Mathematica
says it is April 23. (I've seen the complex code...Yikes!)

EasterSunday[2079]
{2079, 4, 23}

So out of curiosity, I checked with the U.S. Naval Observatory, and it
returned April 23, 2079 also.

http://aa.usno.navy.mil

I can't get a direct link, so click on "Index", then "E", then "Easter, date
of"

Again, just an observation...

HTH
Dana DeLouis
 
Hi Dana!

I think that you're right but....

I've checked with the equations on Claus Tøndering's site and they
show it to be 23-Apr-2079. The formulas I gave (which, should have
been attributed to Chip Pearson's page) and the formula given by Arvi,
all show 16-Apr-2079. John Walkenbach in Excel 2003 Formulas gives a
formula by Thomas Jansen and that one also shows it to be 23-Apr-2079.
Similarly the UDF provided by Chip Pearson shows it to be 23-Apr-2079.

The rules for determining Easter are quite meticulously outlined by
Claus Tøndering at:

http://www.tondering.dk/claus/cal/node3.html

I've converted the formulas used to Excel:

A1: Year Number
G
A2:
=MOD(A1,19)
C
A3:
=INT(A1/100)
H
A4:
=MOD((A3-INT(A3/4)-INT((8*A3+13)/25)+(19*A2)+15),30)
I
A5:
=A4-INT(A4/28)*(1-INT(29/(A4+1))*INT((21-A2)/11))
J
A6:
=MOD(A1+INT(A1/4)+A5+2-A3+INT(A3/4),7)
L
A7:
=A5-A6
Easter Month
A8:
=3+INT((A7+40)/44)
Easter Day
A9:
=(A5-A6)+28-31*INT((3+INT((A5-A6+40)/44))/4)
A10:
=DATE(A1,F8,F9)
For A1 = 2079
Returns Sun 23-Apr-2079


Thomas Jansen's formula referred to by John Walkenbach in Formulas
2003 is:

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6
That returns 23-Apr-2079

I've also looked at the returns's of the UDF provided by Chip Pearson
at:

http://www.cpearson.com/excel/holidays.htm#Easter

This is:

Public Function EasterDate(Yr As Integer) As Date
Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 +
_
d + (d > 48) + 1) Mod 7)
End Function

This returns 23-Apr-2079.

Chip's page also provided the two formulas:

With the four digit year number in A1 and with non-US date settings:

=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34

With the four digit year number in A1 and with US date settings:

=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34

Arvi provided a formula that requires dd-mm-yyyy Regional setting:

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


I've now run a comparison of the returns of all of them.

This Century and last Century they all return the same date with the
exception of 2079

Next Century the UDF is wrong *every single year* until it gets
occasional correct answers in 2900+. (I can say "gets it wrong" with
certainty because the dates returned are not Sundays). So we can rule
out the UDF after 2100.

Also next Century *all* of the formulas return the same dates. So with
the exception of 2079, the formulas all return the same up to 2200.

From after 2200 all except the Claus Tøndering formulas return the
same. Claus Tøndering's formula is different in quite a lot of years
(2204, 2207, 2209, 2017, 2021).

Both the Arvi formula and the ones on Chip Pearson's page both require
modification depending upon the Regional date settings although it
should be possible to convert to a single formula using the
universally accepted yyyy-mm-dd format.

Which formula is right?

I'll go with Thomas Jansen / Claus Tøndering up until 2200. After
that? She'll be right! I'll guess it's Claus Tøndering.

Conclusion?

Use:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

Attribute to Thomas Jansen. But expect an argument in 2204.
 
Hi Dana!

I've now looked at the USNO algorithm. It looks as if it is in error!

Here's a direct reference:
http://tinyurl.com/2kvhp

It returns

Easter 1954 as 25-Apr-1954 but in the text it says 18-Apr-1954
Easter 1981 as 26-Apr-1981 but in the list it says 19-Apr-1981

Other Google searches on Easter 1954 and Easter 1981 are showing the
calculated dates to be wrong.

See for example:
http://www.priu.gov.lk/news_update/Current_Affairs/ca200202/20020224trinity_church.htm
Our Liz reported as going to Good Friday service by Daily News of
17-Apr-1954

There will be errors using the USNO in 2049, 2076, 2106, 2133 and
others.

So that leaves me with the same conclusion:

I'll go with Thomas Jansen / Claus Tøndering up until 2200. After
that? She'll be right! I'll guess it's Claus Tøndering.

Use:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

Attribute to Thomas Jansen. But expect an argument in 2204.
 
Hi Norman. Wow! Thanks for the info. Very interesting. I think this
explains why I said "Yikes" when I saw those equations. :>)
I've now looked at the USNO algorithm. It looks as if it is in error!
It returns
Easter 1954 as 25-Apr-1954 but in the text it says 18-Apr-1954
Easter 1981 as 26-Apr-1981 but in the list it says 19-Apr-1981
Click to expand...

The only explanation I can come up with is the following. One is "Easter
Sunday", and the other is Easter Sunday based on "Greek Orthodox." That's
all I know!

I'll use Mathematica to make it easier for me. For Easter Sunday in 1954
....
Looks like it is either the 18th or 25th, depending...

EasterSunday[1954]
{1954, 4, 18}
EasterSundayGreekOrthodox[1954]
{1954, 4, 25}

Looks like the USNO algorithm was trying to return both dates. I don't have
that link you gave.

For 1981, I get both dates (19 & 26) as you mentioned above.

EasterSunday[1981]
{1981, 4, 19}
EasterSundayGreekOrthodox[1981]
{1981, 4, 26}

Of course, I don't know much about this. Interesting... :>)

Dana DeLouis
 
Hi Dana!

Re: "Yikes"

Agreed <vbg>

I've done the calculations (using Claus Tøndering's algorithm) for
Orthodox Easter and there are lots of differences in the dates.

For Orthodox Easter, you calculate the date using the Julian calendar
and then convert that date to Gregorian. Those dates seem to square
with Orthodox dates from various other sources.

I'm happy that based upon historical records of actual dates of Easter
in 1954 and 1981, the USNO algorithm is wrong.

I've looked at Claus Tøndering's explanation of his algorithm and it
seems that he has correctly transcribed it into his various formulas.

It won't affect accuracy of calculations until 2049. By that date I'll
be able to hide behind Alzheimer's.

I suppose that I could ask the Pope but he's not talking to me since I
told him that the meaning of life was 42.
 
Back
Top