Formula to update dates for holidays

[Laura_Kimball]

Can someone please share what formula I would use to update the day for holidays that change each year? For example, MLK Day, President’s Day, Good Friday, etc. all change each year.

I’m using a social media calendar template and the current formula is not auto-updating the holiday dates for 2020.

[kuovonne]

There is no single easy formula to calculate the date for holidays that change each year. Notice that even the formula for 2019 dates are not accurate. The formula for 2019 simply adds one year to the 2018 dates.

It would be possible to create formulas for holidays that follow only the solar calendar, such as Mother’s Day, which is always the second Sunday in May. However, it would be incredibly difficult (if not impossible) to come up with a formula for Easter, which is based on a combination of both the solar and lunar calendars: the Sunday following the full moon on or after March 21st.

You are probably better off having another field for manually entering the dates for holidays that change every year. Then you can use another a formula field that decides if it should use the manually entered date or calculated date (based on the Date type).

[VictoriaPlummer · Accepted solution]

Here’s a table I built that could help. Basically you build the date based on Month, Week, Day - and compile it into Datetime Format and then Datetime Parse. LMK if you need more explanation here, I built this a while ago so it’s a bit fuzzy.