Undesired result - Can anyone figure out why the change of date referenced in formula.

[brockk]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3							HOLIDAY	# VALUE
4							1/1/2023
5	HOLIDAY	DATE	WKDAY	SALES	ACC SALES		1/16/2023
6		12/16/2023	S				2/20/2023
7		12/17/2023	S				5/29/2023
8		12/18/2023	M				6/19/2023
9		12/19/2023	T				7/4/2023
10		12/20/2023	W				9/4/2023
11		12/21/2023	T				10/9/2023
12		12/22/2023	F				11/23/2023	45253
13		12/23/2023	S				12/25/2023	45285
14		12/24/2023	S
15		12/25/2023	M				IN A15, SHOULD APPEAR "HOLIDAY" BUT IT STAYS IN BLANK.
16		12/26/2023	T				I checked the formula with Evaluate Formula and at 1st stage
17		12/27/2023	W				it reads correctly comparing if lookup of 45285 (12/25/23) is within
18		12/28/2023	T				the lookup range BUT, somehow when I continue to evaluate
19		12/29/2023	F				it magically changes the 45285 date to 45253 (11/23/23) and I
20		12/30/2023	S				don't understand why? I'm using the same formula throughout
21		12/31/2023	S				the whole workbook and with the exception of Christmas' date
22							ALL other occasion presented perfectly. Why is it giving me this
23							issue precisely on the very last Holiday of the year? I'm wondering
24							if there's anything wrong with the formula or is it some type of glitch
25							in Excel? Either way, if any of you Gurus can help me figure this out
26							I would truly appreciate the assist.
27
Sheet1

Cell Formulas
Range	Formula
A6:A21	A6	=IFERROR(IF(LOOKUP($B6,$G$4:$G$13)=$B6,"HOLIDAY",""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:D21	Expression	=$C6="S"	text	NO

Greetings,

Can anyone figure out why when it is comparing whether the dates in column B are Holidays based on a lookup of a range of Holiday dates. It seems that somehow when I verify the formula by means of the Formula --> Evaluate Formula option it initiates calculating correctly searching if B15 (12/25/23 or # value 45285) is identified as a Holiday in the range $G$4:$G$13 and if it finds it, it should identify it as "Holiday" otherwise leave in blank. So as I was saying, it initiates by recognizing the 45285 as the comparison date yet, it magically changes to 45253 somehow? and continues to evaluate the rest of the formula obviously given me an undesired result. The funny thing is on all of the worksheets it has worked flawlessly, its only giving me this issue specifically on this worksheet. Can anyone kindly help me figure where is the issue and if there is a correction for this. Thank you in advance.

 

[Fluff]

Best guess is that some of your dates are text & not proper dates. If you change the cell format for all the dates cells to General, what do you see?

 

[brockk]

Thank Fluff for your quick response. To answer your question I do see that those dates that are exclusive and don't change, like for example New Year's and Christmas, they still appear in the format mm/dd/yyyy as the picture shows. But, those holidays that do change like Thanksgiving which is the 4th Thursday of November of whatever year or Labor Day and/or Memorial day that are 1st and Last Monday of the month respectively are calculated by means of the weekday(date(Y,M,1)) formulas. The biggest issue that I am having is that with all the other dates, I had no problems yet this issue is only with the Xmas date and I can't figure out why?

Book1
	F	G	H	I
2
3		HOLIDAY	# VALUE
4		1/1/2023
5		44942
6		44977
7		45075
8		6/19/2023
9		7/4/2023
10		45173
11		45208
12		45253	45253
13		12/25/2023	45285
14
Sheet1

 

[Fluff]

Those values that look like a date are text which is why the formula doesn't work. You need to change them to proper dates.

 

[brockk]

Awesome suggestion..... dang, something as that actually brought back my blood pressure to normal lol! That did the job! Thank you again @Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.