4-10 holiday schedule formula

[Breizze1313]

I am trying to write a formula that shifts holidays that fall on Fridays or Saturdays to Thursdays. I have the formula to shift holidays that fall on Saturday or Sunday, but the shift to Thursday is alluding me. Please can anyone help with this??

The weekend shift to the Friday before or the Monday after is shown below

Observed Holidays
New Year's Day - Observed - No Work	=MONTH(G56)	=DAY(G56)			=DATE(year,B56,C56)+CHOOSE(WEEKDAY(DATE(year,B56,C56)),1,0,0,0,0,0,-1)
Juneteenth - Observed - No Work	=MONTH(G68)	=DAY(G68)			=DATE(year,B68,C68)+CHOOSE(WEEKDAY(DATE(year,B68,C68)),1,0,0,0,0,0,-1)
Independence Day - Observed - No Work	=MONTH(G69)	=DAY(G69)			=DATE(year,B69,C69)+CHOOSE(WEEKDAY(DATE(year,B69,C69)),1,0,0,0,0,0,-1)
Veterans Day - Observed - No Work	=MONTH(G76)	=DAY(G76)			=DATE(year,B76,C76)+CHOOSE(WEEKDAY(DATE(year,B76,C76)),1,0,0,0,0,0,-1)
Christmas Day - Observed - No Work	=MONTH(G80)	=DAY(G80)			=DATE(year,B80,C80)+CHOOSE(WEEKDAY(DATE(year,B80,C80)),1,0,0,0,0,0,-1)

These are the dates I am specifically looking for right now, and can reverse engineer for others, I'm just stumbling with these specific dates right now.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=DATE(year,B56,C56)+CHOOSE(WEEKDAY(DATE(year,B56,C56)),1,0,0,0,0,-1,-2)

 

[AlanY]

will this works for you?

Book1
	A	B	C	D
1			new date
2	25/12/2023	Monday	25/12/2023	Monday
3	25/12/2024	Wednesday	25/12/2024	Wednesday
4	25/12/2025	Thursday	25/12/2025	Thursday
5	25/12/2026	Friday	24/12/2026	Thursday
6	25/12/2027	Saturday	23/12/2027	Thursday
7	25/12/2028	Monday	25/12/2028	Monday
8	25/12/2029	Tuesday	25/12/2029	Tuesday
9	25/12/2030	Wednesday	25/12/2030	Wednesday
10	25/12/2031	Thursday	25/12/2031	Thursday
11	25/12/2032	Saturday	23/12/2032	Thursday
12	25/12/2033	Sunday	25/12/2033	Sunday
Sheet1

Cell Formulas
Range	Formula
B2:B12,D2:D12	B2	=A2
C2:C12	C2	=IF(WEEKDAY(A2,2)=5,A2-1,IF(WEEKDAY(A2,2)=6,A2-2,A2))
A3:A12	A3	=DATE(YEAR(A2)+1,12,25)