Holiday calculations

[rdhaden]

Is this a bug? Excel tells me that the day before 3/1/1900 was 2/29/1900. Filemaker tells me it was 2/28/1900.

I discovered this trying to calculate holidays. I found a web page with Excel calculations, and was trying to convert them to work in Filemaker. Excel (for windows) treats dates as numbers by counting 1/1/1900 as 1 and numbering each day sequentially thereafter (1/2/1900 is 2, 12/31/1900 is 366, 1/1/1901 is 367, etc.) (excel for mac starts with 1904, but I won't even go into that...)

Anyway, I guess the real question is, does anybody have calculations for holidays for any given year?

[Vaughan]

1900 should not be a leap year. It sounds like excel is not Y19C compatible. <grin>

What holidays did you want to calculate? Some religious holidays work off the phases of the moon and are not going to be easy to program.

[Fitch]

You can thank dj for this:

Date(Month(myDate), 0, Year(myDate))

A cool way to find the last day of the previous month, eh?

And you can thank Gregory XIII for this:

a leap year occurs in all years divisible by 400 or, evenly divisible by 4 and not evenly divisible by 100. The bug is in Excel. 1900 = not a leap year.

FMFiles.com has some calendar downloads, but I don't know if any of them are good for holiday calcs. But I found a good page of calendar links.

Good luck, and I hope you'll share your calcs with us if you come up with some good ones!

"When is Chanukkah this year?"

"Same as always: the 25th of Kislev."

[rdhaden]

"Not y19C compliant." I like that one. I think I'll tell my boss we have to get rid of MS Office because Excel's not y19C compliant.

Since Excel's basic date-to-number system is flawed after the 59th day, I guess those Excel calculations I found are probably useless. I suppose I just need to do more research into why each holiday is on its particular day. Christmas, New Year's Day and July 4th are pretty easy. I was asking the question because the user wants to skip all the data entry she can. It being a University department, the other holidays would definitely include Labor Day, Thanksgiving, Martin Luther King, Jr. Day, and Memorial Day. And Easter, even though it's a Sunday, because this is a University Athletics department with events 7 days a week. And Jewish holidays, so no one calls me racist... And Kwanzaa, for the same reason. Ah, what the hell, since the student body is international, let's just include all holidays for every nation everywhere, no matter what.

Can Filemaker do that?

Thanks for the links. I'll take a closer look at them when I'm not feeling so punchy. For some reason, the whole project is starting to make me giggle. Margaritas, anyone?

[Fitch]

This site looks like a calendar algorithm gold mine.

The CalendarFAQ also has a lot of detail about various algorithms for Easter, etc.

Tidbit: The sequence of Easter dates repeats itself every 5,700,000 years in the Gregorian calendar.

[djgogi]

quote:

---

Originally posted by Fitch:

You can thank

dj

for this:

Date(Month(myDate), 0, Year(myDate))

A cool way to find the last day of the previous month, eh?

---

Thanx, Fitch

just in case you didn't find it yet

Date(Month(myDate)+1, 0, Year(myDate))

is last day of month of date expresed in myDate

Dj

[rdhaden]

Thanks for the help! Here's what I've got so far. Apparently you can't use parentheses in a post here, so I cut and paste it into word and replaced all parentheses with brackets. Most are pretty easy [3rd Monday, 4th Thursday, etc., but wait 'til you see Easter!] The B'nai B'rith site was down, so I haven't gotten Passover, Yom Kippur, and Rosh Hashana yet. I'll post again when [if] I figure them out:

New Year's Day: Date [1, 1, Year]

Martin Luther King Day: Date[1, Case[DayofWeek[Date[1, 1, Year]] <= 2, 21 - [DayofWeek[Date[1, 1, Year]] + 4], 21 - [DayofWeek[Date[1, 1, Year]] - 3]], Year]

President's Day: Date[2, Case[DayofWeek[Date[2, 1, Year]] <= 2, 21 - [DayofWeek[Date[2, 1, Year]] + 4], 21 - [DayofWeek[Date[2, 1, Year]] - 3]], Year]

Memorial Day: Date[5, Case[DayofWeek[Date[5, 1, Year]] = 7, 31, 31 - DayofWeek[Date[5, 1, Year]]], Year]

Independence Day: Date[7, 4, Year]

Labor Day: Date[9, Case[DayofWeek[Date[9, 1, Year]] <= 2, 7 - [DayofWeek[Date[9, 1, Year]] + 4], 7 - [DayofWeek[Date[9, 1, Year]] - 3]], Year]

Veterans' Day: Date[11, 11, Year]

Thanksgiving: Date[11, Case[DayofWeek[Date[11, 1, Year]] <= 5, 27 - DayofWeek[Date[11, 1, Year]], 27 + 7 - [DayofWeek[Date[11, 1, Year]]]], Year]

Christmas: Date[12, 25, Year]

Easter, a simpler to understand version of this appears at www.smart.net/~mmontes/nature1876.html:

Date[Truncate[[Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]+Mod[32+2*Mod[Truncate[Year/100, 0], 4]+2*Truncate[Mod[Year, 100]/4, 0]-Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]-Mod[Mod[Year, 100], 4], 7]-7*Truncate[[Mod[Year, 19]+11*Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]+22*Mod[32+2*Mod[Truncate[Year/100, 0], 4]+2*Truncate[Mod[Year, 100]/4, 0]-Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]-Mod[Mod[Year, 100], 4], 7]]/451, 0]+114]/31, 0], Mod[Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]+Mod[32+2*Mod[Truncate[Year/100, 0], 4]+2*Truncate[Mod[Year, 100]/4, 0]-Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]-Mod[Mod[Year, 100], 4], 7]-7*Truncate[[Mod[Year, 19]+11*Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]+22*Mod[32+2*Mod[Truncate[Year/100, 0], 4]+2*Truncate[Mod[Year, 100]/4, 0]-Mod[19*Mod[Year, 19]+Truncate[Year/100, 0]-Truncate[Truncate[Year/100, 0]/4, 0]-Truncate[[Truncate[Year/100, 0]-Truncate[[Truncate[Year/100, 0]+8]/25, 0]+1]/3, 0]+15, 30]-Mod[Mod[Year, 100], 4], 7]]/451, 0]+114, 31]+1, Year]

Ash Wednesday: Easter - 46

Palm Sunday: Easter - 7

Good Friday: Easter - 2

[rdhaden]

I thought Easter was bad, but here's Rosh Hashanah. This comes from quasar.as.utexas.edu/BillInfo/ReligiousCalendars.html

Set Field [

[rdhaden]

Then Yom Kippur and Passover are easy:

YomKippur = RoshHashanah + 9

Passover = Date[3, 21, Year] + Case[Month[RoshHashanah] = 10, Day[RoshHashanah] + 30, Day[RoshHashanah]]

All three of these Jewish holidays begin at sundown of the day before the date returned by these calculations.