Calculating Holiday Dates in Excel
These are a collection of Excel formulas I use to calculate dates for the Compact Calendar.
$J$1is the numeric year (e.g. 2024)E7is the month (jan=1)F7is the day of week (sun=1)G7is the week (first week=1)
Excel formula: Get date of New Year's Day in a given year
=IF(WEEKDAY(DATE($J$1,1,1))=1,DATE($J$1,1,2),IF(WEEKDAY(DATE($J$1,1,1))=7,DATE($J$1-1,12,31),DATE($J$1,1,1)))
Excel formula: Get observed date (e.g. July 4) in a given year if not last day of month
=IF(WEEKDAY(DATE($J$1,E7,F7))=1,DATE($J$1,E7,F7+1),IF(WEEKDAY(DATE($J$1,E7,F7))=7,DATE($J$1,E7,F7-1),DATE($J$1,E7,F7)))
Excel formula: Get date of Nth day of the Nth week in a month (e.g. 2nd Tuesday in May)
=DATE($J$1,E7,1+((G7-(F7>=WEEKDAY(DATE($J$1,E7,1))))*7)+(F7-WEEKDAY(DATE($J$1,E7,1))))
Excel formula; Last Nth day of the last week in a month (e.g. last Tuesday in May)
=DATE($J$1, E7 + 1, 0) - MOD(WEEKDAY(DATE($J$1, E7 + 1, 0)) - F7, 7)
Excel formula: Easter Sunday date in a given year (based on the Gregorian calendar)
=FLOOR(DATE($J$1,5,DAY(MINUTE($J$1/38)/2+56)),7)-34