Teaser - How do these formulas work to calculate Easter dates?

[Hawkeye249]

=DOLLAR(("4/"&D16)/7+MOD(19*MOD(D16,19)-7,30)*14%,)*7-6
=FLOOR("5/"&DAY(MINUTE(D16/38)/2+56)&"/"&D16,7)-34

I copy/pasted these formulas from a worksheet where a year is typed into D16
and they return the date of Easter for that year.

They both work or worked in Excel - I think I was using 2003 at the time.
However, the first one does not work in the current version Libreoffice Calc.

I do not know how either of them work, and am curious.

Thanks in advance for looking. This is not a homework assignment or test question. (I'm 68 YO)

 

[thisoldman]

Easter: first Sunday after the first full moon on or after the vernal equinox.

I'm still looking at the formulas, but you can get the first formula to work in LibreOffice with this modification:
=DOLLAR(DATE(D16,4,1)/7+MOD(19*MOD(D16,19)-7,30)*0.14,)*7-6

 

[thisoldman]

I think the Western Christian churches use March 21 for the Vernal equinox, without regard to the actual equinox date.

From the Wikipedia article "Metonic cycle", https://en.wikipedia.org/wiki/Metonic_cycle:

​

'For astronomy and calendar studies, the Metonic cycle or Enneadecaeteris (from Ancient Greek: ἐννεακαιδεκαετηρίς, "nineteen years") is a period of very close to 19 years that is nearly a common multiple of the solar year and the synodic (lunar) month.'


​

So now we know the general reasoning for the number 19 in the first formula, and probably for the 38 in the second formula.

 

[Hawkeye249]

I think the Western Christian churches use March 21 for the Vernal equinox, without regard to the actual equinox date.

From the Wikipedia article "Metonic cycle", https://en.wikipedia.org/wiki/Metonic_cycle:

​

'For astronomy and calendar studies, the Metonic cycle or Enneadecaeteris (from Ancient Greek: ἐννεακαιδεκαετηρίς, "nineteen years") is a period of very close to 19 years that is nearly a common multiple of the solar year and the synodic (lunar) month.'


​

So now we know the general reasoning for the number 19 in the first formula, and probably for the 38 in the second formula.

All of you young spreadsheet mechanics - behold the wisdom of ThisOldMan ...

 

[Eric W]

Good information, I didn't know about the Metonic cycle. It makes things a bit clearer. I had assumed that the formulas worked because someone found a function that closely emulates the cycle, and then found a clever way to use Excel functions to make it work. I had never bothered to exactly figure out how it worked.

However, I did assume that since it's just an approximation of the actual astronomical cycles, it wouldn't work in all cases. And such is the case. I tested it many years into the future and compared the results with the actual dates. It works pretty well, the first year that the formula fails is 2079. By checking for that year in the formula, like this:

=IF(Year=2079,65493,FLOOR("5/"&DAY(MINUTE(Year/38)/2+56)&"/"&Year,7)-34)

It is now accurate from 1900 until 2203, long after anyone reading this now is going to care! If I recall my investigations, after that point, the formula starts being wrong more and more often.

 

[Rick Rothstein]

For those who might be interested, I posted a VB function (can be used as a UDF if desired) to calculate Easter (accurate, I believe, to the year 9999) in my mini-blog article here...

http://www.excelfox.com/forum/showt...now-when-Easter-is-this-year?highlight=easter

 

[CONFUSED_AS_USUAL]

That is powerful Rick.

 

[thisoldman]

There's a name for the calculation that computes the Easter date: "Computus." https://en.wikipedia.org/wiki/Computus

For those who want to try their hand at converting from Javascript to Excel, here's a page full of formulas, courtesy of the Wayback Machine: https://web.archive.org/web/20150227133210/http://www.merlyn.demon.co.uk/estralgs.txt