SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-575083%22%20slang%3D%22en-US%22%3EVarying%20Holiday%20Observation%20Date%20based%20on%20DOW%20of%20another%20holiday%20formula%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575083%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20spreadsheet%20has%20a%20Holiday%20tab%20on%20it%20and%20i'm%20trying%20to%20figure%20out%20the%20formula%20needed%20to%20make%20it%20work%2C%20to%20where%20as%20the%20new%20contract%20completion%20date%20on%20Roadway%20Zone%203%20tab%20can%20not%20be%20equal%20to%20a%20date%20the%20holiday%20is%20observed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20for%20a%20State%20holiday%20in%20Dec.%20that%20varies%20depending%20on%20what%20day%20Christmas%20is(12-25--)%20of%20every%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20State%20holiday%20in%20Dec.%20works%20like%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%26nbsp%3BIf%20Christmas%20is%20on%20Saturday%2C%20Christmas%20will%20be%20observed%20on%20Friday%20and%20the%20state%20holiday%20will%20be%20observed%20on%20that%20Thursday%20before.%3C%2FLI%3E%3CLI%3EIf%20Christmas%20is%20on%20Sunday%2C%20Christmas%20will%20be%20observed%20on%20Monday%20and%20the%20state%20holiday%20will%20be%20on%20the%20following%20Tuesday.%3C%2FLI%3E%3CLI%3EIf%20Christmas%20is%20on%20Monday%2C%20the%20state%20holiday%20will%20be%20on%20Tuesday.%3C%2FLI%3E%3CLI%3EIf%20Christmas%20is%20on%20Tuesday%2C%20the%20state%20holiday%20will%20be%20on%20Monday.%3C%2FLI%3E%3CLI%3EIf%20Christmas%20is%20on%20Wednesday%2C%20the%20state%20holiday%20will%20be%20on%20Tuesday.%3C%2FLI%3E%3CLI%3EIf%20Christmas%20is%20on%20Thursday%2C%20the%20state%20holiday%20will%20be%20on%20Friday.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EFor%20example%3A%20in%202021%20Christmas%20is%20on%20Saturday%20which%20will%20be%20observed%20on%20Friday%2C%20therefore%20the%20state%20holiday%20will%20be%20observed%20on%20Thursday.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-575083%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575611%22%20slang%3D%22en-US%22%3ERe%3A%20Varying%20Holiday%20Observation%20Date%20based%20on%20DOW%20of%20another%20holiday%20formula%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339153%22%20target%3D%22_blank%22%3E%40drt_80%3C%2FA%3E%26nbsp%3B%2C%20perhaps%3C%2FP%3E%0A%3CPRE%3E%3DE13%2BCHOOSE(WEEKDAY(E13%2C2)%2C1%2C-1%2C-1%2C1%2C-1%2C-2%2C2)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575844%22%20slang%3D%22en-US%22%3ERe%3A%20Varying%20Holiday%20Observation%20Date%20based%20on%20DOW%20of%20another%20holiday%20formula%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575844%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20was%20it!!%26nbsp%3B%20Thank%20you%20so%20much.%20You%20all%20are%20GREAT!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575903%22%20slang%3D%22en-US%22%3ERe%3A%20Varying%20Holiday%20Observation%20Date%20based%20on%20DOW%20of%20another%20holiday%20formula%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339153%22%20target%3D%22_blank%22%3E%40drt_80%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
drt_80
Occasional Contributor

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
Highlighted
Solution

@drt_80 , perhaps

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

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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies