Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-10-2022, 02:41 PM
Steve Kunkel Steve Kunkel is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 10 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2019
Advanced Beginner
Easter Holiday formulas -- How do they do it!? [low priority post]
 
Join Date: May 2019
Location: Seattle area
Posts: 77
Steve Kunkel is on a distinguished road
Default 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
File Type: xlsx Formulas for Holidays.xlsx (18.4 KB, 4 views)
Reply With Quote
  #2  
Old 11-10-2022, 02:55 PM
macropod's Avatar
macropod macropod is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 10 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 11-11-2022, 01:00 AM
ArviLaanemets ArviLaanemets is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 8 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
Reply With Quote
  #4  
Old 11-11-2022, 08:10 AM
Steve Kunkel Steve Kunkel is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 10 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2021
Advanced Beginner
Easter Holiday formulas -- How do they do it!? [low priority post]
 
Join Date: May 2019
Location: Seattle area
Posts: 77
Steve Kunkel is on a distinguished road
Default

Cool information -- Thanks folks!
Reply With Quote
  #5  
Old 11-13-2022, 05:09 AM
xor xor is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 10 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2021
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

https://www.contextures.com/exceleas...on.html#easter
Reply With Quote
  #6  
Old 11-16-2022, 03:42 PM
Steve Kunkel Steve Kunkel is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 10 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2021
Advanced Beginner
Easter Holiday formulas -- How do they do it!? [low priority post]
 
Join Date: May 2019
Location: Seattle area
Posts: 77
Steve Kunkel is on a distinguished road
Default

Interesting read! Lots of information about the topic here.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Easter Holiday formulas -- How do they do it!? [low priority post] Change template priority via VBA heathkat Word VBA 2 05-28-2018 08:15 PM
Easter Holiday formulas -- How do they do it!? [low priority post] Adding national holiday to Master File BarryB Project 3 04-19-2016 07:43 AM
Microsoft Project holiday integration jeccles123 Project 4 03-22-2015 01:42 PM
Easter Holiday formulas -- How do they do it!? [low priority post] MS Project Holiday pmjunkie Project 3 12-14-2011 09:08 AM
Calender month view peeve - display high priority over low priority appts? ghumdinger Outlook 0 07-25-2011 03:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft