Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Formula to update dates for holidays

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1034 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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).

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.