Holiday Calculations in Excel

Posted Wednesday, December 27, 2023 by Sri. Tagged MEMO
EDITING PHASE:gathering info...

Calculating Holiday Dates in Excel

These are a collection of Excel formulas I use to calculate dates for the Compact Calendar.

  • $J$1 is the numeric year (e.g. 2024)
  • E7 is the month (jan=1)
  • F7 is the day of week (sun=1)
  • G7 is the week (first week=1)

Excel formula: Get date of New Year's Day in a given year


Excel formula: Get observed date (e.g. July 4) in a given year if not last day of month


Excel formula: Get date of Nth day of the Nth week in a month (e.g. 2nd Tuesday in May)


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)