Microsoft Office Forums Easter Holiday formulas -- How do they do it!? [low priority post]
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-10-2022, 02:41 PM
 Steve Kunkel Windows 10 Office 2019 Advanced Beginner Join Date: May 2019 Location: Seattle area Posts: 77
Easter Holiday formulas -- How do they do it!? [low priority post]

Attached is a group of formulas for US holidays that I found on the web a long time ago. (Though I did write the DST ones myself )

I've found 2 different formulas (on the web) that will calculate Easter.

Code:
```=DOLLAR(("4/"&theyear)/7+MOD(19*MOD(theyear,19)-7,30)*14%,)*7-6
=ROUND(DATE(theyear,4,MOD(234-11*MOD(theyear,19),30))/7,0)* 7-6```
I'm just curious if anyone here created either of these... I'm wondering: What method does a person even use to write one of these?

Something like "the second Sunday of March" can be systematically figured out, since there are only 7 possible permutations of what a "March" month can look like... But Easter is based on the full moon! As I found on the web:

Quote:
 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.
The only thing I can figure, is that these were somehow "reverse-engineered." I have no idea how that would be done though...

I hand-checked for the next 10 Easters, and both of these formulas appeared to yield correct results...
Attached Files
 Formulas for Holidays.xlsx (18.4 KB, 2 views)
#2
11-10-2022, 02:55 PM
 macropod Windows 10 Office 2016 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 21,927

In the western calendar, Easter dates follow the 19-year lunar cycle, hence the 19 in the formulae.

Do note that those formulae don't correctly calculate Easter dates for the orthodox churches. Orthodox churches celebrate Easter on the basis of the Julian calendar. Until the Gregorian calendar was introduced in 1582, Orthodox churches celebrated Easter Sunday on the same date as Western churches. Since then, the Orthodox churches have often celebrated Easter Sunday on different dates from Western churches. In some years the Orthodox Easter Sunday occurs on the same day as the Western Easter Sunday. In most years, however, Orthodox Easter follows Western Easter by at least one week.

In Word, you can do both sets of Easter calculations, with fields coded as:
Code:
```{QUOTE
{ASK Year "For what Year do you want the date of Easter?" \d {DATE \@ YYYY}}
{SET yr{Year}}
{SET a{=MOD(yr,19)}}
{SET b{=INT(yr/100)}}
{SET c{=MOD(yr,100)}}
{SET d{=INT(b/4)}}
{SET e{=MOD(b,4)}}
{SET f{=(b+8)/25}}
{SET g{=INT((b-f+1)/3)}}
{SET h{=MOD((19*a+b-d-g+15),30)}}
{SET i{=INT(c/4)}}
{SET k{=MOD(c,4)}}
{SET l{=MOD((32+2*e+2*i-h-k),7)}}
{SET m{=INT((a+11*h+22*l)/451)}}
{SET Gm{=INT((h+l-7*m+114)/31)}}
{SET Gd{=MOD((h+l-7*m+114),31)+1}}
"In {yr}, the Western Easter Sunday {={QUOTE"{yr}{Gm \# 00}{Gd \# 00}" \@ YYYYMMdd }-{DATE \@ YYYYMMdd} \# falls;fell} on the (Gregorian) {Gd \* Ordinal} of {QUOTE "{Gm}/0" \@ MMMM}."}```
and:
Code:
```{QUOTE
{SET G{=MOD(yr,19)}}
{SET I{=MOD((19*G+15),30)}}
{SET J{=MOD((yr+INT(yr/4)+I),7)}}
{SET Om{=3+INT((I-J+40)/44)}}
{SET Od{=I-J+28-31*INT(Om/4)}}
{SET a{=INT((14-Om)/12)}}
{SET b{=yy+4800-a}}
{SET c{=Om+12*a-3}}
{SET jd{=Od+INT((153*c+2)/5)+365*b+INT(b/4)-32083}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET Gd1{=h-INT((153*i+2)/5)+1}}
{SET Gm1{=i+3-12*INT(i/10)}}
"In the same year, the Orthodox Easter Sunday {={QUOTE"{yr}{Gm1 \# 00}{Gd1 \# 00}" \@ YYYYMMdd }-{DATE \@ YYYYMMdd} \# falls;fell} on the (Julian) {Od \* Ordinal} of {QUOTE "{Om}/0" \@ MMMM}. This equates to the (Gregorian) {Gd1 \* Ordinal} of {QUOTE "{Gm1}/0" \@ MMMM}."}```
For those who are interested about the Orthodox Easter date calculation, in the above field:
G is the so-called “Golden Number”-1
I is the number of days from 21 March to the Paschal full moon
J is the weekday of the Paschal full moon (0=Sunday, 1=Monday, etc.)
I-J is the number of days from 21 March to the Sunday on or before the Paschal full moon.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
#3
11-11-2022, 01:00 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 762

I use formulas:
Code:
```Easter Sunday:
=ROUND((DAY(MINUTE(YearNo/38)/2+55)&".4."& YearNo)/7,)*7-6
Easter Friday:
=ROUND((DAY(MINUTE(YearNo/38)/2+55)&".4."& YearNo)/7,)*7-6-2
First Pentecost:
=ROUND((DAY(MINUTE(YearNo/38)/2+55)&".4."& YearNo)/7,)*7-6+49
etc.```
Got them from some german Excel newsgroup posting some 15+ years ago

I ported this formula into SQL Server (as an UDF) too, but there another one is working somewhat better (i.e. it is slightly faster):
Code:
```USE [DBName]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_Easter] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_Easter] (@Year INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @Date DATETIME
DECLARE @c INT
DECLARE @n INT
DECLARE @i INT
DECLARE @k INT
DECLARE @j INT
DECLARE @l INT
DECLARE @m INT
DECLARE @d INT
SET @n = @Year - 19 * (@Year / 19)
SET @c = @Year / 100
SET @k = (@c - 17) / 25
SET @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15
SET @i = @i - 30 * (@i / 30)
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n)
/ 11))
SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
SET @j = @j - 7 * (@j / 7)
SET @l = @i - @j
SET @m = 3 + (@l + 40) / 44
SET @d = @l + 28 - 31 * (@m / 4)
SET @Date = CAST(@Year AS VARCHAR) + '-' + CAST(@m AS VARCHAR) + '-' +
CAST(@d AS VARCHAR)
RETURN @Date
END```
#4
11-11-2022, 08:10 AM
 Steve Kunkel Windows 10 Office 2021 Advanced Beginner Join Date: May 2019 Location: Seattle area Posts: 77

Cool information -- Thanks folks!
#5
11-13-2022, 05:09 AM
 xor Windows 10 Office 2021 Expert Join Date: Oct 2015 Posts: 1,081

#6
11-16-2022, 03:42 PM
 Steve Kunkel Windows 10 Office 2021 Advanced Beginner Join Date: May 2019 Location: Seattle area Posts: 77

Quote:
 Originally Posted by xor https://www.contextures.com/exceleas...on.html#easter

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post heathkat Word VBA 2 05-28-2018 08:15 PM BarryB Project 3 04-19-2016 07:43 AM jeccles123 Project 4 03-22-2015 01:42 PM pmjunkie Project 3 12-14-2011 09:08 AM ghumdinger Outlook 0 07-25-2011 03:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:41 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top