#1
|
|||
|
|||
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 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:
I hand-checked for the next 10 Easters, and both of these formulas appeared to yield correct results... |
#2
|
||||
|
||||
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}."} 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}."} 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
|
|||
|
|||
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. 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
|
|||
|
|||
Cool information -- Thanks folks!
|
#5
|
|||
|
|||
|
#6
|
|||
|
|||
Quote:
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Change template priority via VBA | heathkat | Word VBA | 2 | 05-28-2018 08:15 PM |
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 |
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 |