Help

Re: Formula to update dates for holidays

Solved
Jump to Solution
819 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Laura_Kimball
4 - Data Explorer
4 - Data Explorer

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.Screen Shot 2020-05-04 at 10.42.25 AM

1 Solution

Accepted Solutions
VictoriaPlummer
7 - App Architect
7 - App Architect

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.

See Solution in Thread

2 Replies 2

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
7 - App Architect
7 - App Architect

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.