re Easterdate
Steved
Norman Gave this formula which gives me 23 April 2079
so instead of using 2079 as a number I used as date
ie Year (A2) A2=2079 formatted "yyyy"
=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-33
Thankyou everyone for the interest in this subject.
Because of the moon we go to these lenghts to have an
answer. Intriguing to one thoughts
Thankyou.
Norman Harker
The solution that covers 2079 as well as all other years until at
least 2200 would appear to be:
Thomas Jansen formula:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6
But the problem with the other formulas is almost certainly due to a
lunar miscalculation.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au
Daniel.M
> But the problem with the other formulas is almost certainly due to a
> lunar miscalculation.
I doubt they rely on any real lunar calculation. Jansen's formula (as well as
Norbert Hetterich's very short formula, the one having problem with year 2079)
are very ingenuous ways trying to 'fit' the years 1900-2200 easter dates not
unlike a regression would do.
They are very good at it but wait a little longer and they'll grow beserk! :-)
Hetterich's formula misses on more than 13% of the 2500-3000 easter dates (which
is a proof they do not rely on precise lunar calcs). But that's perfectly OK
with me (and you as well I guess).
One problem is they are not 1904-calendar compatible.
FWIW:
For the only formula I know that is fully 1900-1904 calendar compatible and that
works on ALL 1900-2200 years, I like a lot Gerhard Somitsch implementation
because it's a brutal (in your face!) regression without any desire to hide it
:-)
=DATE(A1,3,29.56+0.979*MOD(204-11*MOD(A1,19),30)-
WEEKDAY(DATE(A1,3,28.56+0.979*MOD(204-11*MOD(A1,19),30))))
One can see the formula to find the previous Sunday in there.
Big Cheers to Gerhard :-)
Daniel M.
Norman Harker
I've compared with the other formulas. I confirm your conclusions for
years up to 2203.
Gerhard Somitsch's formula returns the same as Jansen's for all years
as long as you are on the 1900 date system. As soon as you switch
systems, Jansen's goes wrong; it returns Saturdays (just adding 1
doesn't correct it).
Somitsch's formula doesn't return differently with a change of date
system.
The Tondering algorithm matches Somitsch up until 2204, after which
there are quite a lot of differences. Which is right in 2204? I
suspect that Tondering is right as he is directly applying the stated
algorithm.
I could megaformula Tondering but that would be very ugly. So maybe we
should stick with Somitsch if only to keep the Mac guys happy.
Norman Harker
I've converted Claus Tondering's algorithm to a UDF
Function EASTER(year As Single) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/node3
'Norman Harker 10-Jul-2004
Dim G As Integer: Dim C As Integer: Dim H As Integer
Dim I As Integer: Dim J As Integer: Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer
G = year Mod 19
C = Int(year / 100)
H = (C - Int(C / 4) - Int((8 * C + 13) / 25) + 19 * G + 15) Mod 30
I = H - Int(H / 28) * (1 - Int(29 / (H + 1)) * Int((21 - G) / 11))
J = (year + Int(year / 4) + I + 2 - C + Int(C / 4)) Mod 7
L = I - J
EM = 3 + Int((L + 40) / 44)
ED = L + 28 - 31 * Int(EM / 4)
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
EASTER = DateSerial(year, EM, ED) - Adj1904
End Function
This seems to test OK under both date systems so "Mac challenged"
users can be happy.
It returns the same as the Somitsch formula up to 2203 and thereafter
appears to be an accurate interpretation of the rules set out by Claus
Tondering.
There is no error in 2079 which was the problem with the two formulas
on Chip Pearson's page.
There are no errors for 1954 and 1981 which was where the USNO
algorithm was wrong.
It's not reliant upon any specific Regional Settings on dates.
I'm sure the Pope will rest easier knowing that the date for Easter
can be determined up to 9999. Didn't like the old fella worrying about
it.
Daniel.M
Thanks for the VBA code.
> I'm sure the Pope will rest easier knowing that the date for Easter
> can be determined up to 9999. Didn't like the old fella worrying about
> it.
A while back in m.p.e.fr, F. Sigonneau and I proposed to have all the easter
dates listed in a column beside the years number, and use a binary search to
grab the one for a given year. All this could be packed in an XLA.
Not a bad idea after all since it's very fast (it does very few comparisons:
around 14 comps for around 10000 years) but it does take the fun out of it :-))
Regards,
Daniel M.
Norman Harker
I'm not all that happy with the code. In general I tend to use the Int
function rather than the \ operator. But in this case there's so many
of them I think that the following is better.
Function EASTER(year As Single) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/node3
'Norman Harker 10-Jul-2004
Dim G As Integer: Dim C As Integer: Dim H As Integer
Dim I As Integer: Dim J As Integer: Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer
G = year Mod 19
C = year \ 100
H = (C - C \ 4 - (8 * C + 13) \ 25 + 19 * G + 15) Mod 30
I = H - H \ 28 * (1 - 29 \ (H + 1) * (21 - G) \ 11)
J = (year + year \ 4 + I + 2 - C + C \ 4) Mod 7
L = I - J
EM = 3 + (L + 40) \ 44
ED = L + 28 - 31 * EM \ 4
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
EASTER = DateSerial(year, EM, ED) - Adj1904
End Function
Now to sort out Orthodox Easter!
Daniel.M
> I'm not all that happy with the code.
Following is an Easter algo available on Laurent Longre's site (picked up from
an Excel ng).
It works flawlessly for year 1900-9999.
The variables are self-explanatory.
And you can adapt for 1904 (the same way you did here with yours).
Regards,
Daniel M.
' La fonction suivante est tirée d'un forum anglophone.
' Elle renvoie la date du dimanche de Pâques pour toute
' année située entre 1900 et 9999.
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
Norman Harker
Thanks for tracking that one down. I do like to have two different
methods for cross checking purposes and I'll run Laurent's up against
mine tomorrow.
Meanwhiles, no sooner did I get back from the Vatican, than the Greeks
came knocking on my door saying that now they're the Euro Champions,
they deserve a bit of respect. And what about Orthodox Easter!
So here's the code for that one plus a minor correction to the earlier
post that was revealed by the dreaded Ctrl + Alt + F9
Function EASTER(year As Single) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/node3
'Norman Harker 10-Jul-2004
Dim G As Integer: Dim C As Integer: Dim H As Integer
Dim I As Integer: Dim J As Integer: Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer
G = year Mod 19
C = year \ 100
H = (C - C \ 4 - (8 * C + 13) \ 25 + 19 * G + 15) Mod 30
I = H - (H \ 28) * (1 - (29 \ (H + 1)) * ((21 - G) \ 11))
J = (year + year \ 4 + I + 2 - C + C \ 4) Mod 7
L = I - J
EM = 3 + (L + 40) \ 44
ED = L + 28 - (31 * (EM \ 4))
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
EASTER = DateSerial(year, EM, ED) - Adj1904
End Function
Function ORTHODOXEASTER(year As Single) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/node3
'Norman Harker 11-Jul-2004
Dim G As Integer: Dim I As Integer: Dim J As Integer
Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer: Dim JulAdj As Integer
G = year Mod 19
I = (19 * G + 15) Mod 30
J = (year + year \ 4 + I) Mod 7
L = I - J
EM = 3 + (L + 40) \ 44
ED = L + 28 - (31 * (EM \ 4))
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
JulAdj = 10 + (year \ 100) - 16 - (year \ 400) + 4
ORTHODOXEASTER = DateSerial(year, EM, ED) - Adj1904 + JulAdj
End Function
The Orthodox Easter algorithm is based upon the Julian Calendar so
I've had to make a double adjustment. One for the 1904 Date System and
one for the Julian > Gregorian uplift.
The calculations of Orthodox Easter seem to square with the multi cell
formula version and with records I have of Orthodox Easter.
Your previous post was interesting! I wonder if a database of the
dates without formulas in an Addin's worksheet would be a more
efficient way of handling the problem. Use the formulas to establish
the dates, parallel to the year numbers. Copy > Paste Special Values.
Then use VBA to just look up the date for the input year.
I'll put in attributions for in the final versions.
I've left unsnipped so as to get Laurent's code and the above in a
single post.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au
"Daniel.M" <prenom...@bigfoot.inutil.com> wrote in message
news:%23YmdS7p...@TK2MSFTNGP11.phx.gbl...
Norman Harker
Here is what looks like my final effort on this subject. I've merged
the code and introduced an optional argument that allows choice
between Western and Orthodox with the default being Western.
I've tested under both date systems and it seems OK for dates in the
range 1900 (or 1904) to 9999.
Function EASTER(year As Single, Optional Orthodox As Boolean) As Date
'Based on Claus Tondering algorithm interpretation.
'See http://www.tondering.dk/claus/cal/node3
'Norman Harker 12-Jul-2004 with acknowledgements to
'Chip Pearson, Daniel M, Dana DeLouis
Dim G As Integer: Dim C As Integer: Dim H As Integer
Dim I As Integer: Dim J As Integer: Dim L As Integer
Dim EM As Integer: Dim ED As Integer
Dim Adj1904 As Integer
Dim JulAdj As Integer
If IsMissing(Orthodox) Then Orthodox = False
G = year Mod 19
C = year \ 100
H = (C - C \ 4 - (8 * C + 13) \ 25 + 19 * G + 15) Mod 30
If Orthodox = False Then
I = H - (H \ 28) * (1 - (29 \ (H + 1)) * ((21 - G) \ 11))
J = (year + year \ 4 + I + 2 - C + C \ 4) Mod 7
JulAdj = 0
Else
I = (19 * G + 15) Mod 30
J = (year + year \ 4 + I) Mod 7
JulAdj = 10 + (year \ 100) - 16 - (year \ 400) + 4
End If
L = I - J
EM = 3 + (L + 40) \ 44
ED = L + 28 - (31 * (EM \ 4))
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
EASTER = DateSerial(year, EM, ED) - Adj1904 + JulAdj
End Function
Daniel.M
> I've tested under both date systems and it seems OK for dates in the
> range 1900 (or 1904) to 9999.
It seems Ok here as well.
And the Greeks will be happy (definitively _their_ year) :-)
Regards,
Daniel M.
keepITcool
only an optional variant or object can have the error value missing...
an optional boolean will be false (when not set to true)
Thus the line Ismissing(Orthodox).. can be deleted
is there a reason for the way you decalre like
dim x:Dim y?
Iso..
> Dim G As Integer: Dim C As Integer: Dim H As Integer
> Dim I As Integer: Dim J As Integer: Dim L As Integer
> Dim EM As Integer: Dim ED As Integer
> Dim Adj1904 As Integer
> Dim JulAdj As Integer
I would do..
Dim G%,I%,H%,I%,J,%,L%,EM%,ED%,adj1904%,adjJul%
(i've renamed juladj to adjJul for consistency..
Also..
why do you define year as Single?
wouldn't yyyy as Integer be more logical and explanatory?
as they say in Dutch "it's f*cking ants" but
while you're at it make it nice
<g>
btw:
10 + (year \ 100) - 16 - (year \ 400) + 4
could be
C - C\4 -2
<vbg>
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
Norman Harker
Thanks.
Made some changes as per your suggestions; I never pretend to do
things in the most efficient way! In fact I write home to Mum when my
coding works.
There's a few other simplifications possible but they tend to obscure
the logic. The AdjJul for dates after 1900 is a case in point although
your simplification is the formula usually used for the conversion so
it's a cleaner approach than one that I built for 1900 + only.
The %(Integer), &(long), !(Single), #(Double), @(Currency), and
$(String) declarations I've never liked. It's a matter of taste and I
wouldn't expect people who see my ties to accept my views on taste.
You're probably right.
Dana DeLouis
additional thoughts.
On the part that accounts for 1904 system..
If ActiveWorkbook.Date1904 = True Then
Adj1904 = 365 * 4 + 2
End If
This adjustment would only be appropriate if the function was called from
the worksheet.
If a workbook was set to 1904, and you ran vba code like
If Now() > EASTER(2005) Then
MsgBox "The year is 2004 ?.."
End If
Today, (2004) one would expect False, but here, one would get the MsgBox
(returned Easter would be in the year 2001).
I'm not sure what a good method is to use when you have functions that
return dates. Do the pro's want 1 function for vba calls, and another
function to use on worksheets where one could account for the 1904 system?
I don't know.
One idea I have in my Easter function uses the following idea, but I noticed
the extra code slows it down. The code I use is a little different, so I
left that part out.
Select Case TypeName(Application.Caller)
Case "Range" ' A Worksheet call, return dependant on Date1904
Easter = (..My Code..) -
IIf(Application.Caller.Parent.Parent.Date1904, 1462, 0)
Case "Error" 'Vba Function
Easter = (..My Code..)
Case Else ' Not accounted for
Easter = "Error"
End Select
Again, I'm not sure what a good technique is when you want to account for
1904 on a worksheet, but perhaps use it in vba as well.
Dana DeLouis
"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:ekik256Z...@TK2MSFTNGP11.phx.gbl...
Norman Harker
Thanks. I'd not even thought of VBA calls!
In passing; what is the prevailing view on declaring variables using
%(Integer), &(long), !(Single), #(Double), @(Currency)?
Dana DeLouis
Have a function (Like Easter) calculate it normally with a Windows default
of a 1900 system. This would make vba code faster, and would work on "most"
default systems using 1900. On those occasions where you would like to
adjust a worksheet custom function that returns a date, based on the
1900/1904 system, then wrap that function in an Aux function. I was think
of something like this: (True resulting in -1462)
Function Adj1904(dte)
Adj1904 = dte + 1462 * (Application.Caller.Parent.Parent.Date1904)
End Function
Then, you could just use
=Easter(2005), on both a worksheet, or vba.
If you wish to adjust for different time systems, then wrap it like this:
=adj1904(Easter(2005))
I'm not sure if this is the best way. One advantage I see here is that such
a function could be applied to other date functions as well if one wanted to
toggle between time systems. If would also serve as an easy reminder that
the function will automatically adjust the dates. And if you don't think
you'll use 1904, you could just remove it.
I think the Mac folks have to do this backwards since I believe their
default is 1904. Anyway, just some ideas I was messing with.
That's a good question on using Dim G& vs Dim G as long.
Dana DeLouis
"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:%23axOyiB...@TK2MSFTNGP11.phx.gbl...
Norman Harker
Good idea. At least in the 999 cases out of 1000, the function isn't
doing the unnecessary work on calculating the not required adjustment.
In most applications though the calculation would be hardly noticeable
as we only need to calculate Easter once (or a few times). However,
with a lot of date calculations a wrapper would be a better way to go.
I've known of the Dim G& approach which appears to be a Basic thing
but rarely see it used. I suppose most people like to see the obvious
declaration and to use a system that can be used for all
possibilities.