Automating Company Holidays on calendar

[saracat2012]

Hello again wonderful people. I have a partially automated calendar Excel file I track my time off hours in for work. I've been upgrading it a little at a time, as I learn new functionality and I've gotten to a point that seems to be very close, but is stumping me. I've extracted the relevant portion. I'm trying to get the yellow cells to auto-populate the dates when I change the year in the a reference cell (green one for this example). The array has formulas that calculate the date of specific holidays based on the year (1st section), uses a UCF to show what day the holiday falls on (2nd section), and which days the company grants for those holidays (3rd section). How do I use this information correctly to achieve the stated goal?

Bonus scenario (orange cells): The holiday given around the 4th of July changes depending on the relationship to the weekend. Ex: If 7/4 falls on a Thursday, 7/5 is given off. If it falls on a Tuesday, 7/3 is given off. This is reflected in the 3rd section of the array (the days that are given off say the name of the day). How do I make it auto-populate the holiday label (orange cells), based on the year?

As always, TIA.

Holiday sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Holiday	When	2024	2025	2026	2027	2028				Holiday			Date
2	New Years Day	1-Jan	1/1/24	1/1/25	1/1/26	1/1/27	1/1/28		2024		New Year's			1/2
3	MLK Jr. Day	3rd Monday in Jan	1/15/24	1/20/25	1/19/26	1/18/27	1/17/28				MLK Jr. Day			1/16
4	Presidents Day	3rd Monday in Feb	2/19/24	2/17/25	2/16/26	2/15/27	2/21/28				President's Day			2/20
5	Memorial Day	Last Monday in May	5/27/24	5/26/25	5/25/26	5/31/27	5/29/28				Memorial Day			5/29
6	July 3rd	3-Jul	7/3/24	7/3/25	7/3/26	7/3/27	7/3/28				3rd of July			7/3
7	Independence Day	4-Jul	7/4/24	7/4/25	7/4/26	7/4/27	7/4/28				Independence Day			7/4
8	July 5th	5-Jul	7/5/24	7/5/25	7/5/26	7/5/27	7/5/28				Labor Day			9/4
9	Labor Day	1st Monday in Sept	9/2/24	9/1/25	9/7/26	9/6/27	9/4/28				Thanksgiving			11/23
10	Thanksgiving Day	4th Thurs in Nov	11/28/24	11/27/25	11/26/26	11/25/27	11/23/28				Black Friday			11/24
11	Black Friday	4th Fri in Nov	11/29/24	11/28/25	11/27/26	11/26/27	11/24/28				Christmas Eve			12/25
12	Christmas Eve	Dec. 24	12/24/24	12/24/25	12/24/26	12/24/27	12/24/28				Christmas Day			12/26
13	Christmas Day	Dec.25	12/25/24	12/25/25	12/25/26	12/25/27	12/25/28				Floating Holiday			7/14
14	Holiday	Falls on
15	New Years Day		Monday	Wednesday	Thursday	Friday	Saturday
16	MLK Jr. Day		Monday	Monday	Monday	Monday	Monday
17	Presidents Day		Monday	Monday	Monday	Monday	Monday
18	Memorial Day		Monday	Monday	Monday	Monday	Monday
19	July 3rd		Wednesday	Thursday	Friday	Saturday	Monday
20	Independence Day		Thursday	Friday	Saturday	Sunday	Tuesday
21	July 5th		Friday	Saturday	Sunday	Monday	Wednesday
22	Labor Day		Monday	Monday	Monday	Monday	Monday
23	Thanksgiving Day		Thursday	Thursday	Thursday	Thursday	Thursday
24	Black Friday		Friday	Friday	Friday	Friday	Friday
25	Christmas Eve		Tuesday	Wednesday	Thursday	Friday	Sunday
26	Christmas Day		Wednesday	Thursday	Friday	Saturday	Monday
27	Holiday	Company holiday
28	New Years Day		Monday	Wednesday	Thursday	Friday	Monday
29	MLK Jr. Day		Monday	Monday	Monday	Monday	Monday
30	Presidents Day		Monday	Monday	Monday	Monday	Monday
31	Memorial Day		Monday	Monday	Monday	Monday	Monday
32	July 3rd				Friday	Friday	Monday
33	Independence Day		Thursday	Friday	Monday		Tuesday
34	July 5th		Friday	Monday		Monday
35	Labor Day		Monday	Monday	Monday	Monday	Monday
36	Thanksgiving Day		Thursday	Thursday	Thursday	Thursday	Thursday
37	Black Friday		Friday	Friday	Friday	Friday	Friday
38	Christmas Eve		Tuesday	Wednesday	Thursday	Friday	Monday
39	Christmas Day		Wednesday	Thursday	Friday	Friday	Tuesday
40
Holidays

Cell Formulas
Range	Formula
C2:G2	C2	=DATE(C1,1,1)
C3:G3	C3	=DATE(C1,1,1)+14+CHOOSE(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2)
C4:G4	C4	=DATE(C1,2,1)+14+CHOOSE(WEEKDAY(DATE(C1,2,1)),1,0,6,5,4,3,2)
C5:G5	C5	=DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6))
C6:G6	C6	=DATE(C1,7,3)
C7:G7	C7	=DATE(C1,7,4)
C8:G8	C8	=DATE(C1,7,5)
C9:G9	C9	=DATE(C1,9,1)+CHOOSE(WEEKDAY(DATE(C1,9,1)),1,0,6,5,4,3,2)
C10:G10	C10	=DATE(C1,11,1)+21+CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5)
C11:G11	C11	=DATE(C1,11,1)+22+CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5)
C12:G12	C12	=DATE(C1,12,24)
C13:G13	C13	=DATE(C1,12,25)
C15:G26	C15	=mydayname(C2)
C28:G28	C28	=IF(mydayname(C2)="Saturday",mydayname(C2+2),IF(mydayname(C2)="Sunday",mydayname(C2+1),mydayname(C2)))
C29:G31,C35:G37	C29	=mydayname(C3)
C32:G32	C32	=IF(mydayname(C7)="Tuesday",mydayname(C6),IF(mydayname(C7)="Wednesday",mydayname(C6),IF(mydayname(C7)="Saturday",mydayname(C6),IF(mydayname(C7)="Sunday",mydayname(C6-1),""))))
C33:G33	C33	=IF(mydayname(C7)="Saturday","Monday",IF(mydayname(C7)="Sunday","",mydayname(C7)))
C34:G34	C34	=IF(mydayname(C7)="Monday", mydayname(C8), IF(mydayname(C7)="Thursday", mydayname(C8), IF(mydayname(C7)="Friday", mydayname(C8+2), IF(mydayname(C7)="Sunday", mydayname(C8), " "))))
C38:G38	C38	=IF(mydayname(C12)="Saturday",mydayname(C12-1),IF(mydayname(C12)="Sunday",mydayname(C12+1),mydayname(C12)))
C39:G39	C39	=IF(mydayname(C13)="Sunday",mydayname(C13+1),IF(mydayname(C13)="Monday",mydayname(C13+1),IF(mydayname(C13)="Saturday",mydayname(C13+6),mydayname(C13))))

 

[Tetra201]

Here are formulas for the fixed-weekday-of-month holidays:

=WORKDAY.INTL((I2&"-1-1")-1,3,"0111111")   'MLK Jr. Day
=WORKDAY.INTL((I2&"-2-1")-1,3,"0111111")   'President's Day
=WORKDAY.INTL((I2&"-6-1"),-1,"0111111")   'Memorial Day
=WORKDAY.INTL((I2&"-9-1")-1,1,"0111111")   'Labor Day
=WORKDAY.INTL((I2&"-11-1")-1,4,"1110111")   'Thanksgiving
=WORKDAY.INTL((I2&"-11-1")-1,4,"1111011")   'Black Friday

To come up with formulas for fixed-date holidays, please describe how each of them is handled when they fall on Saturdays and Sundays.

 

[saracat2012]

Thanks for the reply. Those work great! The rules for the fixed date holidays are different for each one. For New Year's Day, if it falls on a Saturday or Sunday, it is always the following Monday (could be as late as 1/3). For the 2 days of Christmas, they are given on the closest working day (i.e. falls on Saturday and Sunday, given on Friday and Monday) unless they fall on a Fri-Sat combo, then one is given Friday (12/24) and the other is given the following Friday (12/31), or they fall on a Sun-Mon combo, in which they are given Mon-Tues. It's convoluted, to say the least. I had to go back through my records for the last 10 years to see how they all played out.

 

[Tetra201]

Here is an installment for the New Year and Christmas holidays:

=WORKDAY.INTL((I2&"-1-1")-1,1,"0000011")    'New Year Day
=WORKDAY.INTL(WORKDAY.INTL((I2&"-12-24")+1,-1,"0000010")-1,1,"0000001")    'Christmas Eve
=WORKDAY.INTL(WORKDAY.INTL((I2&"-12-25")-1,1,"1000000")-1,1,"0000001")+6*(WEEKDAY(I2&"-12-25")=7)    'Christmas Day

 

[saracat2012]

I did find one issue with the Black Friday formula. In 2024, the 4th Friday is the week prior to the 4th Thursday (since the 1st is on a Friday). I was able to rectify this (I think permanently) by just referencing the Thanksgiving cell and adding 1 ("={T.G. cell} +1").