I"m having trouble with an IF OR statement in EXCEL using a date field. My goal is to look at a cell and identify the date as a holiday or Sunday. The format of the statement works with numbers, but I have had no luck with it when the field is a date. Here is the formula that I need fixed.
=IF((OR(D2=DATEVALUE(12/25/2010),D2=DATEVALUE(10/11/2010),D2=DATEVALUE(11/11/2010),D2=DATEVALUE(11/25/2010),D2=DATEVALUE(1/1/2011),D2=DATEVALUE(1/17/2011),D2=DATEVALUE(2/21/2011),D2=DATEVALUE(5/30/2011),D2=DATEVALUE(7/4/2011),D2=DATEVALUE(9/5/2011)),"Holiday","Sunday")

Hi,

The dates inside the DATEVALUE() function need to be written as strings, like this:

DATEVALUE("11/11/2010")


You could simplify your formula significantly if you were willing to use up a small amount of spreadsheet space to create a holiday table which could then be referenced using MATCH().

Keeping all of the dates in the formula makes it long and harder to maintain, but I was able to simplify it to this:

=IF(OR(D2={40537;40462;40493;40507;40544;40560; 40595;40693;40728;40791}),"Holiday","Sunday")


It's worth noting that this formula is based on the same logic as yours: if D2 isn't a holiday then the formula assumes it is a Sunday.