# 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(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 is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

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

## 2 thoughts on “Excel Use Date in IF Condition”

1. MF says:

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.

2. MF says:

just curious, why my gravatar’s image did not show up… ~_~