# Thread: dates in IF statements in EXCEL

1. Registered User
Join Date
Nov 2011
Posts
1

## Unanswered: dates in IF statements in EXCEL

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")

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•