Calculate Easter on a dynamic calendar

[Taz68rt]

I have an extremely hard excel challenge. I have been trying to create a formula that will accurately pick the correct day for Easter. In a dynamic calendar. What I have found is that it is based on the first new moon that falls between March 21st and April 30th. The new moon occurs every 29.5 days. Which you must multiply (29.5 days multiplied by 24hrs multiplied by 60 Minutes multiplied by 60 Seconds)to find the start of the new moon. Which I have done and still can’t find a constant to use for any formula to work. Can anyone help me with this?

 

[James006]

Hi,
For Easter date, you can test

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

 

[Skybluekid]

You may need to approach it in a different way. The actual definition of Easter is "The simple standard definition of Easter is that it is the first Sunday after the full Moon that occurs on or after the spring equinox. If the full Moon falls on a Sunday then Easter is the next Sunday." So your first constant is March 21st, You will then need then a table of the Full Moon that occurs for each year between March 22 and April 25. This will then give you a second date, you will be able work out the first Sunday from that point.

 

[DRSteele]

You may need to approach it in a different way. The actual definition of Easter is "The simple standard definition of Easter is that it is the first Sunday after the full Moon that occurs on or after the spring equinox. If the full Moon falls on a Sunday then Easter is the next Sunday." So your first constant is March 21st, You will then need then a table of the Full Moon that occurs for each year between March 22 and April 25. This will then give you a second date, you will be able work out the first Sunday from that point.

It's not the full moon that counts, but rather the paschal full moon. You will need to ascertain those dates first. This article might help. So might this for a discussion about Easter dates in general.

 

[MARK858]

Hi,
For Easter date, you can test

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

Just to say that A1 contains the year in the formula and for the formula to work the date format regional setting on the computer must be day/month/year

It also only is accurate to 2078

 

[Artik]

A more accurate calculation of the date of Easter can be found here. The many steps of the calculation prompt you to create a function in VBA.

Artik

 

[Dave Patton]

a2 has year Try with the next 200 years

=FLOOR(DATE(A2,5,DAY(MINUTE(A2/38)/2+56)),7)-34+(A2=2079)*7

 

[MARK858]

A more accurate calculation of the date of Easter can be found here. The many steps of the calculation prompt you to create a function in VBA.

Artik

Artik for UDF see the U.S. Naval Observatory option in the link below. You'll also see the formula James006 posted in passing (it is possibly where he got it from as is a well known page).

Excel Easter Calculations How to Find Easter Date Formula

How to use Excel to calculate the date for Easter, using worksheet formulas and user defined functions

www.contextures.com

@Dave Patton the formula in post 7 matches the U.S. Naval Observatory UDF until 2204 when the formula returns 15/04/2204 rather than 22/04/2204