Excel 2007 and Date of Easter

[capterdi]

Hi,

Perhaps somebody knows if Excel 2007 will have available a date&time formula to calculate the date of Easter (Christian Feast)? Up to now this calculation is only possible via a Sub.

Or is it now too early to know about such details?

Thank you
:dunno

[Bob Phillips]

This is a re-print of a response previously on this topic by Norman Harker

Easter is the first Sunday after the first full moon after the vernal
equinox.

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.
But the official full moon may differ from the real full moon by one
or two days.

All these issues and methods of calculation are covered at:

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

IOW, it ain't easy.

[capterdi]

Yes, it?s not a simple calculation...

I have tested this Function...seem?s to work.

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

[Bob Phillips]

It seems to go wrong for 2099 to 2123

[Aussiebear]

If I'm still alive then Bob, something is definately wrong.

[Cyberdude]

Here's my routine for determining the Easter date:
Function EasterDte(Optional YearVal&) As Date '10/20/06 [Called by "EasterDate" and "HolidayDates"]
Dim Cent&, Mth&, Day&, N&, K&, I&, J&, L&, I1&, I2&, I3&, I4&
Dim J1&, J2&, J3&, Msg$
Const Title$ = "'Personal' (EasterDte)"
If YearVal = 0 _
Then
Msg = "Enter the 4-digit year:"
YearVal = Application.InputBox(Msg, Title, Default:=Year(Date) - (Month(Date) > 4), Type:=3)
If YearVal = 0 Then GoTo Finish 'If "CANCEL", exit sub
End If
Cent = Application.Floor(YearVal / 100, 1)
N = YearVal - 19 * Application.Floor(YearVal / 19, 1)
K = Application.Floor((Cent - 17) / 25, 1)
I = Cent - Application.Floor(Cent / 4, 1)
I = I - Application.Floor((Cent - K) / 3, 1) + 19 * N + 15
I = I - 30 * Application.Floor(I / 30, 1)
I1 = Application.Floor(I / 28, 1)
I2 = 1 - I1
I3 = Application.Floor(29 / (I + 1), 1)
I4 = Application.Floor((21 - N) / 11, 1)
I = I - I1 * I2 * I3 * I4
J1 = YearVal + Application.Floor(YearVal / 4, 1)
J2 = J1 + I + 2 - Cent
J3 = Application.Floor(Cent / 4, 1)
J = J2 + J3
L = I - (J - 7 * Application.Floor(J / 7, 1))
Mth = 3 + Application.Floor((L + 40) / 44, 1)
Day = L + 28 - (31 * Application.Floor(Mth / 4, 1))
EasterDte = DateValue(Mth & "/" & Day & "/" & YearVal)
Finish:
End Function 'EasterDte'
It's clumsy, but seems to be correct.

[capterdi]

I will try your rutine, but allow some time. I have a couple of things to do right now...thanks.

[capterdi]

Please don?t forget about the main question:

Until now, we have had to resort to Functions, Subs, etc. to get this date. Is it too early to know if Excel 2007 has this Function integrated to the other date/time functions? Does someone have an idea?

[Bob Phillips]

There is nothing built-in within Excel 2007 anymore than in 2003.

[Bob Phillips]

Dude,

That is no more accurate than capterdi's, in fact it gets 3 more wrong.

[mdmackillop]

Yet another!

Function EasterDate(y As Long)
'http://www.assa.org.au/edm.html#Computer
' EASTER DATE CALCULATION FOR YEARS 1583 TO 4099
' y is a 4 digit year 1583 to 4099
' d returns the day of the month of Easter
' m returns the month of Easter
' Easter Sunday is the Sunday following the Paschal Full Moon
' (PFM) date for the year
' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
' The \ operator may be unfamiliar - it means integer division
' for example, 30 \ 7 = 4 (the remainder is ignored)
' All variables are integer data types
' It's free! Please do not modify code or comments!
' ==========================================================
Dim FirstDig, Remain19, temp 'intermediate results
Dim tA, tB, tC, tD, tE 'table A to E results
FirstDig = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19
' calculate PFM date
temp = (FirstDig - 15) \ 2 + 202 - 11 * Remain19

Select Case FirstDig
Case 21, 24, 25, 27 To 32, 34, 35, 38
temp = temp - 1
Case 33, 36, 37, 39, 40
temp = temp - 2
End Select
temp = temp Mod 30
tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 > 10) Then tA = tA - 1
'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - FirstDig) Mod 4
If tC = 3 Then tC = tC + 1
If tC > 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE
'return the date
If d > 31 Then
d = d - 31
m = 4
Else
m = 3
End If
EasterDate = DateValue(d & "/" & m & "/" & y)
End Function

[Emily]

Ecclesiastical Rules
http://astro.nmsu.edu/~lhuber/leaphist.html

[capterdi]

Dude,

That is no more accurate than capterdi's, in fact it gets 3 more wrong.

Dude,

Do you have a routine to check this? Perhaps, you could share it?

Thanks

:yes

[Gert Jan]

Up to now this calculation is only possible via a Sub.


Hi,
I found a formula that calculates Easter-date, it is a local(Dutch) and i dont know how it should be translated, but no doubt someone else here can.
It is not something i came up with, (i don't even understand it), i got it a few years back, don't remember who it came from.



=GULDEN(("4/"&B1)/7+REST(19*REST(B1;19)-7;30)*14%;)*7-6

where in this case cell B1 is the year

Gert Jan

found it again, it was a lifetimecalendar, hereby attached. The formula is in L2

[Wolfgang]

...and another one...shamelessly stolen from another guru...

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

...as long as nobody asks me to explain this...

Best,
Wolfgang

[Cyberdude]

No, I don't have a way to check out my routine. I stole it from somewhere a few years ago, and the source led me to believe it is correct. I can't help but wonder how you know what is and what isn't correct?? Knowing that mine is incorrect suggests that you have one that somehow you know IS correct. And how do you know IT is correct?? Hmmmm. Chicken and egg problem.

[mdmackillop]

Here's a comparison table. I've no reason to suppose my source is more correct than any others, but feel free to plug in your own versions.

[stanl]

I think after 4099 it is all a moot point. Wonder what the Hooters Calendar will look like then:devil2: Stan

[stanl]

and my contribution


Easter = Mid(WorksheetFunction.Dollar(DateSerial(intYear, 4, 1) / 7 + ((19 * (intYear Mod 19) - 7) Mod 30) * 0.14, 0), 2) * 7 - 6

[Wolfgang]

Hi Malcolm...

Supie solution...

Here is the one by Carl Friedrich Gauss who said that the phrase "I don't like Mondays" does not apply for Easter Mondays...

Please have a look...

Best,
Wolfgang

PS: For Nerds Only...
'Osterberechnung f?r die Jahre von 1583 bis 4099
'von Ronald W. Mallen mit den Originalkommentaren
SUB Mallen(Jahr&, Ostern%)
'It's free! Please do not modify code or comments!
IF (Jahr& MOD 4 = 0 AND Jahr& MOD 100 <> 0) OR Jahr& MOD 400 = 0 THEN Feb% = 29 ELSE Feb% = 28
FirstDig& = INT (Jahr& / 100) 'first 2 digits of year
Remain19& = Jahr& MOD 19 'remainder of year / 19
'calculate PFM date
temp& = INT((FirstDig& - 15) / 2 + 202 - 11 * Remain19&)
IF FirstDig& > 26 THEN temp& = temp& - 1
IF FirstDig& > 38 THEN temp& = temp& - 1
IF ((FirstDig& = 21) OR (FirstDig& = 24) OR (FirstDig& = 25) OR (FirstDig& = 33) OR (FirstDig& = 36) OR (FirstDig& = 37)) THEN temp& = temp& - 1
temp& = temp& MOD 30
tA& = temp& + 21
IF temp& = 29 THEN tA& = tA& - 1
IF (temp& = 28 AND Remain19& > 10) THEN tA& = tA& - 1
'find the next Sunday
tB& = (tA& - 19) MOD 7
tC& = (40 - FirstDig&) MOD 4
IF tC& = 3 THEN tC& = tC& + 1
IF tC& > 1 THEN tC& = tC& + 1
temp& = Jahr& MOD 100
tD& = INT (temp& + temp& / 4) MOD 7
tE& = ((20 - tB& - tC& - tD&) MOD 7) + 1
d& = tA& + tE&
'return the date
IF d& > 31 THEN
d& = d& - 31
m& = 4
ELSE
m& = 3
END IF
Ostern% = 31 + Feb% + d& - 1: IF m& = 4 THEN Ostern% = Ostern% + 31
END SUB

[capterdi]

Yet another!

Function EasterDate(y As Long)
'http://www.assa.org.au/edm.html#Computer
' EASTER DATE CALCULATION FOR YEARS 1583 TO 4099
' y is a 4 digit year 1583 to 4099
' d returns the day of the month of Easter
' m returns the month of Easter
' Easter Sunday is the Sunday following the Paschal Full Moon
' (PFM) date for the year
' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
' The \ operator may be unfamiliar - it means integer division
' for example, 30 \ 7 = 4 (the remainder is ignored)
' All variables are integer data types
' It's free! Please do not modify code or comments!
' ==========================================================
Dim FirstDig, Remain19, temp 'intermediate results
Dim tA, tB, tC, tD, tE 'table A to E results
FirstDig = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19
' calculate PFM date
temp = (FirstDig - 15) \ 2 + 202 - 11 * Remain19

Select Case FirstDig
Case 21, 24, 25, 27 To 32, 34, 35, 38
temp = temp - 1
Case 33, 36, 37, 39, 40
temp = temp - 2
End Select
temp = temp Mod 30
tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 > 10) Then tA = tA - 1
'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - FirstDig) Mod 4
If tC = 3 Then tC = tC + 1
If tC > 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE
'return the date
If d > 31 Then
d = d - 31
m = 4
Else
m = 3
End If
EasterDate = DateValue(d & "/" & m & "/" & y)
End Function


Hi,

I notice that for years belonging to XIX century and older ones, the algorithm doesn?t return a valid date.

:think:

[mdmackillop]

Change the penultimate line to

EasterDate = d & "/" & m & "/" & y

[capterdi]

:friends:

I want to thank you all of you for sharing so many routines for this calculations..!!!
:rotlaugh:

[Carl A]

Formula by Thomas Jansen
=DOLLAR(("4/"&A14)/7+MOD(19*MOD(A14,19)-7,30)*14%, )*7-6
This Formula looks like the formula submitted that was in Dutch
:2p:

[capterdi]

Since the date of Easter depends on the date of a full moon, can someone help with deriving the algorithm to get the dates of the 4 moon phases? For example: the user types the month/year that he wants, and the algorithm returns the 4 moon dates for that month?

[mdmackillop]

Try entering +moon +phase +calculate in Google etc. No shortage of methods. If you need help with a particular solution, let us know.