Excel Use Date in IF Condition (worksheet solution)
If you use a Text as a condition in IF, you can simply use double quote on the text such as “Text”, but to use Date in IF condition, you cannot simply use double quote on a date.
Lets recap how we apply condition on different data type.
To apply condition on Number:
=IF(A1>5,"large number","small number")
To apply condition on Text:
What about Date? Should we just use double quote as we did for Text or without double quote?
Unfortunately, none of these will work. A lot of people do not know how to do it, instead they just work around by typing 1/1/2015 in a Range as the condition
This is an ugly workaround because you need an extra Cell to achieve the task. In fact, there are several solutions to use Date in IF condition as described below.
Solution 1: Use +0 after a date
Add a +0 after double quoting the date.
If you just type “1/1/2015”, it is a Text. But if you add 0, Excel will guess the data type of “1/1/2015” before adding 0, and return data as Date after calculation.
Whether the converted data type is dd/mm or mm/dd depends on your local setting in Control Panel.
Solution 2: Use DateValue() Function
Syntax of DateValue
DATEVALUE( date in text )
DateValue Function takes a text parameter of Date and then return a date in a numeric value. Excel considers 1/1/1900 as the first date, and +1 for each date onwards.
If you want to know more about date and time conversion, you can click here.
Note that you don’t need to convert A1 to DateValue, because the underlying value of a Date is already a numeric value, the Date format dd/mm/yyyy you see is just a format of display.
Solution 3: Use Date() Function
Syntax of Date()
DATE( year, month, day )
This is the least recommended way to use. In Date() Function, you need to pass a year, month, day values and then convert them back into a complete Date. I don’t recommend this method because I feel inconvenient to break a date into pieces and then put them back together again.
Use Date for comparison in VBA
It is easier to use Date in IF condition in VBA, you only need to add sharp before and after a Date
IF Range(“A3”).value > #1/1/2015# THEN ….