Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

460 views

Skip to first unread message

Jul 9, 2004, 5:55:20 PM7/9/04

to

Hello from 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.

Jul 9, 2004, 8:47:56 PM7/9/04

to

Hi Steved!

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

Jul 9, 2004, 10:15:01 PM7/9/04

to

Hi Norman,

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

Jul 9, 2004, 11:35:54 PM7/9/04

to

Hi Daniel!

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.

Jul 10, 2004, 2:22:35 AM7/10/04

to

Hi Daniel!

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.

Jul 10, 2004, 12:19:57 PM7/10/04

to

Hi Norman,

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.

Jul 10, 2004, 12:45:43 PM7/10/04

to

Hi Daniel!

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!

Jul 10, 2004, 1:01:35 PM7/10/04

to

Norman,

> 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

Jul 10, 2004, 2:51:38 PM7/10/04

to

Hi Daniel!

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

Jul 11, 2004, 12:38:39 PM7/11/04

to

Hi Daniel!

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

Jul 11, 2004, 5:21:56 PM7/11/04

to

Hi,

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

Jul 11, 2004, 7:44:12 PM7/11/04

to

Norman..

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 >

Jul 11, 2004, 9:20:42 PM7/11/04

to

Hi KeepITcool!

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.

Jul 12, 2004, 9:41:02 AM7/12/04

to

Hi Norman. I don't have a good solution myself yet, but here are some

additional thoughts.

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

Jul 12, 2004, 10:01:08 AM7/12/04

to

Hi Dana!

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

Jul 12, 2004, 11:32:50 AM7/12/04

to

Hi Norman. How about this idea?

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

Jul 12, 2004, 5:44:10 PM7/12/04

to

Hi Dana!

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.

0 new messages

Search

Clear search

Close search

Google apps

Main menu