• 414K Members
• 8,071 Online
• 478K Conversations
SOLVED

## Varying Holiday Observation Date based on DOW of another holiday formula help.

Highlighted
Occasional Contributor

# Varying Holiday Observation Date based on DOW of another holiday formula help.

Hello,

The attached spreadsheet has a Holiday tab on it and i'm trying to figure out the formula needed to make it work, to where as the new contract completion date on Roadway Zone 3 tab can not be equal to a date the holiday is observed.

It is for a State holiday in Dec. that varies depending on what day Christmas is(12-25--) of every year.

The State holiday in Dec. works like this.

•  If Christmas is on Saturday, Christmas will be observed on Friday and the state holiday will be observed on that Thursday before.
• If Christmas is on Sunday, Christmas will be observed on Monday and the state holiday will be on the following Tuesday.
• If Christmas is on Monday, the state holiday will be on Tuesday.
• If Christmas is on Tuesday, the state holiday will be on Monday.
• If Christmas is on Wednesday, the state holiday will be on Tuesday.
• If Christmas is on Thursday, the state holiday will be on Friday.

For example: in 2021 Christmas is on Saturday which will be observed on Friday, therefore the state holiday will be observed on Thursday.

3 Replies
Solution

# Re: Varying Holiday Observation Date based on DOW of another holiday formula help.

@drt_80 , perhaps

`=E13+CHOOSE(WEEKDAY(E13,2),1,-1,-1,1,-1,-2,2)`

# Re: Varying Holiday Observation Date based on DOW of another holiday formula help.

That was it!!  Thank you so much. You all are GREAT!@Sergei Baklan

# Re: Varying Holiday Observation Date based on DOW of another holiday formula help.

@drt_80 , you are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies