Excel Use Date in IF Condition

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:

=IF(A1=”John”,”My friend”,”Stranger”)

What about Date? Should we just use double quote as we did for Text or without double quote?

=IF(A1>1/1/2015,"present","past")  OR
=IF(A1>"1/1/2015","present","past")  ??????

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

=IF(A1>A2,"present","past")

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(A1>”1/1/2015″+0,”present”,”past”)

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.

=IF(A1>DateValue(“1/1/2015″),”present”,”past”)

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.

=IF(A1>Date(2015,1,1),"present","past")

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 ….

Outbound References

http://www.techonthenet.com/excel/formulas/datevalue.php

http://www.techonthenet.com/excel/formulas/date.php

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Excel Use Date in IF Condition

  1. Yes. Agree with you that a lot of ppl are not aware of how to deal with Date correctly.
    But also, it’s confusing enough that some functions like COUNTIF would aceept date with double quote… 😛
    (because most of the time it is accomplished with an operator, e.g. “>”, “>=”…. that basically perform what the “+0” does in your explanation.

Leave a Reply

Your email address will not be published. Required fields are marked *