Adding text to cell if conditional formatting changes color based on what day the holiday falls on.

[cbye]

Hi, I am trying to figure out how to add text, (FH meaning Fixed Holiday) to two consecutive cells (Thanksgiving and the day after Thanksginving) that conditional formatting turns orange based on the year and day on a calendar. I have been able to do this for the other holidays similar to Thanksgiving (Memorial day and Labor day) that could fall on different days on a calendar depending on the year. I just can't figure out how to get both days to display the FH. Also, it appears that xl2bb is displaying the months as black bars. As well as the year selector.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1																								Vacation Days Left				Optional Holidays Left
2												V	Vacation		FH	Fixed Holiday			OH	Optinal Holiday				25				4
3																													Select a Year
4		January																											2024
5		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
6		FH														FH
7
8
9		February
10		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	1	2
11
12
13
14		March
15		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
16
17
18
19		April
20		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
21
22
23
24		May
25		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
26																												FH
27
28
29		June
30		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
31
32
33
34		July
35		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
36					FH
37
38
39		August
40		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
41
42
43
44		September
45		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
46			FH
47
48
49		October
50		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
51
52
53
54		November
55		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
56
57
58
59		December
60		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
61																										FH
vacation tracker

Cell Formulas
Range	Formula
X2	X2	=AH19-BP27
AB2	AB2	=AH20-BP42
B5:AF5	B5	=DATE(AC4,1,0)+SEQUENCE(1,31)
I6:O6	I6	=IF(I5=AI5, "FH", "")
P6	P6	=IF(P5=AO5, "FH", "")
Q6	Q6	=IF(Q5=AO5, "FH", "")
R6	R6	=IF(R5=AO5, "FH", "")
S6	S6	=IF(S5=AO5, "FH", "")
T6	T6	=IF(T5=AO5, "FH", "")
U6	U6	=IF(U5=AO5, "FH", "")
V6	V6	=IF(V5=AO5, "FH", "")
W6	W6	=IF(W5=AO5, "FH", "")
B10:AF10	B10	=DATE(AC4,2,0)+SEQUENCE(1,31)
B15:AF15	B15	=DATE(AC4,3,0)+SEQUENCE(1,31)
B20:AF20	B20	=DATE(AC4,4,0)+SEQUENCE(1,31)
B25:AF25	B25	=DATE(AC4,5,0)+SEQUENCE(1,31)
Y26	Y26	=IF(Y25=AO6, "FH", "")
Z26	Z26	=IF(Z25=AO6, "FH", "")
AA26	AA26	=IF(AA25=AO6, "FH", "")
AB26	AB26	=IF(E35=AO7, "FH", "")
AC26	AC26	=IF(AC25=AO6, "FH", "")
AD26	AD26	=IF(AD25=AO6, "FH", "")
AE26	AE26	=IF(AE25=AO6, "FH", "")
B30:AF30	B30	=DATE(AC4,6,0)+SEQUENCE(1,31)
B35:AF35	B35	=DATE(AC4,7,0)+SEQUENCE(1,31)
E36	E36	=IF(E35=AO7, "FH", "")
B40:AF40	B40	=DATE(AC4,8,0)+SEQUENCE(1,31)
B45:AF45	B45	=DATE(AC4,9,0)+SEQUENCE(1,31)
B46	B46	=IF(B45=AO8, "FH", "")
C46	C46	=IF(C45=AO8, "FH", "")
D46	D46	=IF(D45=AO8, "FH", "")
E46	E46	=IF(E45=AO8, "FH", "")
F46	F46	=IF(F45=AO8, "FH", "")
G46	G46	=IF(G45=AO8, "FH", "")
H46	H46	=IF(H45=AO8, "FH", "")
I46	I46	=IF(I45=AO8, "FH", "")
B50:AF50	B50	=DATE(AC4,10,0)+SEQUENCE(1,31)
B55:AF55	B55	=DATE(AC4,11,0)+SEQUENCE(1,31)
B60:AF60	B60	=DATE(AC4,12,0)+SEQUENCE(1,31)
Z61	Z61	=IF(Z60=AO11, "FH", "")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B60:AF60	Expression	=WEEKDAY(B60,2)>5	text	NO
B55:AF55	Expression	=WEEKDAY(B55,2)>5	text	NO
B50:AF50	Expression	=WEEKDAY(B50,2)>5	text	NO
B45:AF45	Expression	=WEEKDAY(B45,2)>5	text	NO
B40:AF40	Expression	=WEEKDAY(B40,2)>5	text	NO
B35:AF35	Expression	=WEEKDAY(B35,2)>5	text	NO
B30:AF30	Expression	=WEEKDAY(B30,2)>5	text	NO
B25:AF25	Expression	=WEEKDAY(B25,2)>5	text	NO
B20:AF20	Expression	=WEEKDAY(B20,2)>5	text	NO
B15:AF15	Expression	=WEEKDAY(B15,2)>5	text	NO
B10:AF10	Expression	=WEEKDAY(B10,2)>5	text	NO
B5:AF5	Expression	=WEEKDAY(B5,2)>5	text	NO
B6:AF6,B11:AF11,B16:AF16,B21:AF21,B26:AF26,B31:AF31,B36:AF36,B41:AF41,B46:AF46,B51:AF51,B56:AF56,B61:AF61	Cell Value	="OH"	text	NO
B6:AF6,B11:AF11,B16:AF16,B21:AF21,B26:AF26,B31:AF31,B36:AF36,B41:AF41,B46:AF46,B51:AF51,B56:AF56,B61:AF61	Cell Value	="V"	text	NO
B55:AF55	Expression	=MONTH(B55)<>MONTH($K$55)	text	NO
B45:AF45	Expression	=MONTH(B45)<>MONTH($K$45)	text	NO
B30:AF30	Expression	=MONTH(B30)<>MONTH($K$30)	text	NO
B20:AF20	Expression	=MONTH(B20)<>MONTH($K$20)	text	NO
B10:AF10	Expression	=MONTH(B10)<>MONTH($K$10)	text	NO
B6:AF6	Expression	=ISNUMBER(VLOOKUP(B5,$AO$4:$AO$11,1,0))	text	NO
B11:AF11,B16:AF16,B21:AF21,B26:AF26,B36:AF36,B46:AF46,B56:AF56,B61:AF61	Expression	=ISNUMBER(VLOOKUP(B10,$AO$4:$AO$11,1,0))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AC4:AF4	List	=$AG$24:$AG$46
B6:AF6	List	=$AG$14:$AG$17
B36:AF36	List	=$AG$14:$AG$17
B46:AF46	List	=$AG$14:$AG$17
B61:AF61	List	=$AG$14:$AG$17
B51:AF51	List	=$AG$14:$AG$17
B11:AF11	List	=$AG$14:$AG$17
B16:AF16	List	=$AG$14:$AG$17
B21:AF21	List	=$AG$14:$AG$17
B26:AF26	List	=$AG$14:$AG$17
B31:AF31	List	=$AG$14:$AG$17
B41:AF41	List	=$AG$14:$AG$17

Book1.xlsx
	AK	AL	AM	AN	AO
3	Holiday	When			Date
4	New Years Day	Janurary 1			1/1/2024
5	Martin Luther King Jr. Day	The 3rd Monday in Janurary			1/15/2024
6	Memoral Day	The last Monday in May			5/27/2024
7	Independence Day	4-Jul			7/4/2024
8	Labor Day	The first Monday in September			9/2/2024
9	Thanksgiving Day	The 4th Thursday in November			11/28/2024
10	Day after Thanksgiving Day	The 4th Friday in November			11/29/2024
11	Christmas Day	25-Dec			12/25/2024
vacation tracker

Cell Formulas
Range	Formula
AO4	AO4	=DATE(AC4,1,1)
AO5	AO5	=DATE(AC4,1,1+14+CHOOSE(WEEKDAY(DATE(AC4,1,1)),1,0,6,5,4,3,2))
AO6	AO6	=DATE(AC4,6,1)-WEEKDAY(DATE(AC4,6,6))
AO7	AO7	=DATE(AC4,7,4)
AO8	AO8	=DATE(AC4,9,1)+CHOOSE(WEEKDAY(DATE(AC4,9,1)),1,0,6,5,4,3,2)
AO9	AO9	=DATE(AC4,11,1)+21+CHOOSE(WEEKDAY(DATE(AC4,11,1)),4,3,2,1,0,6,5)
AO10	AO10	=DATE(AC4,11,1)+22+CHOOSE(WEEKDAY(DATE(AC4,11,1)),4,3,2,1,0,6,5)
AO11	AO11	=DATE(AC4,12,25)

 

[6StringJazzer]

I am able to copy your post, reformat the black, and then paste in the XLBB correctly, you may have some other formatting issues.

Also you should get rid of the merged cells.

The day after Thanksgiving is not always the 4th Friday of November. If Nov. 1 falls on a Friday, it will be the 5th Friday.

You are using formulas to show FH but you do not have any formulas in the cells for Thanksgiving and day after Thanksgiving. You seem to be using a different formula for every month based on knowing what holiday is near that date. I would use a single formula, and use the same formula for every day.

Starting in Jan 1 (B6):

=IF(ISERROR(MATCH(B5,$AO:$AO,0)),"","FH")

Then copy to all other dates. That should solve your Thanksgiving problem. Also if you ever have to add new holidays, you just add them in AK:AO and you don't have to change any formulas.

$scratch.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
54	November
55	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
56																												FH	FH
Sheet2

Cell Formulas
Range	Formula
B55:AF55	B55	=DATE(AC4,11,0)+SEQUENCE(1,31)
B56:AF56	B56	=IF(ISERROR(MATCH(B55,$AO:$AO,0)),"","FH")
Dynamic array formulas.

 

[6StringJazzer]

I removed the merged cell and formatted it as Center Across Selection. The XLBB code does not handle that. Here is what it actually looks like after I changed it, which has the same appearance as the merged cells but avoids all the problems:

 

[cbye]

Hi Jeff, thanks for taking the time to reply back. Your formula does add the FH to the cells that I need. But as you mentioned, its not always going to be on the 28th and 29th. What didn't come across when I uploaded my sheet was in the merged columns, AC4, I have a Data Validation list that I can choose a year. And based on that, I have formulas in all of my dates to choose the right day based on the year at AC4. For instance the Thanksgiving date at AO9 I have a formula to always choose the 4th Thursdays in November, =DATE(AC4,11,1)+21+CHOOSE(WEEKDAY(DATE(AC4,11,1)),4,3,2,1,0,6,5). Then based on that I have conditional formatting that will turn those dates on the calendar day the orange color. Then I had to figure out how to add the FH to all of the orange cells on the calendar. If you look at September, for Labor day, I have an IF formula that if the date cell, C45 equals the date for Labor day in AO8 then put FH in the cell otherwise "". I applied that formula to all days that Labor day can fall on for any year. And if I change the year at AC4, it dynamically changes the days on the calendar to match that year and adds the orange conditional formatting for all fixed holidays as well as adds FH to the proper date.
I'm sure there are more stream lined ways of doing this but Im still a student of Excel and am learning new things every time I try to tweak this sheet.
Back to Thanksgiving, if I only needed to do Thanksgiving day, I would do the same thing I did for Labor day. But because these days are consecutive, I can't figure out how to apply the formula to both days no matter what year it is.

 

[6StringJazzer]

Then I had to figure out how to add the FH to all of the orange cells on the calendar.

I think that's inside out, and probably more complicated than it needs to be. You just need the right formula to put "FH" where you need it, then use conditional formatting to turn any cell containing "FH" to orange.

I'll take a deeper look at this when time permits.

 

[cbye]

Your probably right. As I said, Im still learning. And projects like these are how I learn to make things more proficient. I don't need to tell you, in excel there is more than one way to skin a vacation calendar. Thanks for your help.