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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies