The IF function is one of the most useful Excel functions. It is used to test a condition and return one value if the condition is TRUE and another if it is FALSE.

One of the most common applications of the IF function involves the comparison of values.

These values can be numbers, text, or even dates. However, using the IF statement with date values is not as intuitive as it may seem.

In this tutorial, I will demonstrate some ways in which you can **use the IF function with date values**.

## Syntax and Usage of the IF Function in Excel

The syntax for the IF function is as follows:

IF(logical_test, [value_if_true], [value_if_false])

Here,

*logical_test*is the condition or criteria that you want the IF function to test. The result of this parameter is either TRUE or FALSE*value_if_true*is the value that you want the IF function to return if the*logical_test*evaluates to TRUE*value_if_false*is the value that you want the IF function to return if the*logical_test*evaluates to FALSE

For example, say you want to write a statement that will return the value “*yes*” if the value in cell reference A2 is equal to 10, and “*no*” if it’s anything but 10.

You can then use the following IF function for this scenario:

=IF(A2=10,"yes","no")

## Comparing Dates in Excel (Using Operators)

Unlike numbers and strings, comparison operators, when used with dates, have a slightly different meaning.

Here are some of the comparison operators that you can use when comparing dates, along with what they mean:

Operator | What it Means When Using with Dates |
---|---|

< | Before the given date |

= | Same as the date with which we are comparing |

> | After the given date |

<= | Same as or before the given date |

>= | Same as or after the given date |

## Using IF Function with Dates in Excel

It may look like IF formulas for dates are the same as IF functions for numeric or text values, since they use the same comparison operators.

However, it’s not as simple as that.

Unfortunately, unlike other Excel functions, the IF function cannot recognize dates.

It interprets them as regular text values.

So you cannot use a logical test such as *“>05/07/2021*” in your IF function, as it will simply see the value “*05/07/2021*” as text.

Here are a few ways in which you can incorporate date values into your IF function’s *logical_test* parameter.

### Using the IF Function with DATEVALUE Function

If you want to use a date in your IF function’s logical test, you can wrap the date in the DATEVALUE function.

This function converts a date in text format to a serial number that Excel can recognize as a date.

If you put a date within quotes, it is essentially a text or string value.

When you pass this as a parameter to the DATEVALUE function, it takes a look at the text inside the double quotes, identifies it as a date and then converts it to an actual Excel date value.

Let us say you have a date in cell A2, and you want cell B2 to display the value “*done*” if the date comes before or on the same date as “*05/07/2021*” and display “*not done*” otherwise.

You can use the IF function along with DATEVALUE in cell B2 as follows:

=IF(A2<DATEVALUE("05/07/2021"),"done","not done")

Here’s a screenshot to illustrate the effect of the above formula:

Also read: How to Add Days to a Date in Excel

### Using the IF Function with the TODAY Function

If you want to compare a date with the current date, you can use the IF function with the TODAY function in the logical test.

Let’s say you have a date in cell A2 and you want cell B2 to display the value “done” if it is a date before today’s date.

If not, you want let’s say you want to display the value “not done”. You can use the IF function along with the TODAY function in cell B2 as follows:

=IF(A2<TODAY(),"done","not done")

Here’s a screenshot to illustrate the effect of the above formula (assuming the current date is 05/08/2021):

### Using the IF Function with Future or Past Dates

An interesting thing about dates in Excel is that you can perform addition and subtraction operations with them too.

This is because dates are basically stored in Excel as serial numbers, starting from the date *Jan 1, 1900*.

Each day after that is represented by one whole number.

So, the serial number 2 corresponds to *Jan 2, 1900,* and so on.

This means that adding *n* number of days to a date is equivalent to adding the value *n* to the serial number that the date represents.

If TODAY() is *05/07/2021*, then TODAY()+5 is five days after today, or *05/12/2021*. Similarly, TODAY()-3 is three days before today or *05/04/2021*.

Let’s say you have a date in cell A2 and you want cell B2 to mark it as “*within range*” if it is within 15 days from the current date.

If not, you want to show “*out of range*”. You can use the IF function along with the TODAY function in cell B2 as follows:

=IF(A2<TODAY()+15,"within range","out of range")

Here’s a screenshot to illustrate the effect of the above formula (assuming the current date is 05/08/2021):

## Points to Remember

Having discussed different ways to use dates with the IF function, here are some important points to remember:

- Instead of hardcoding the dates into the IF function’s logical test parameter, you can store the date in a separate cell and refer to it with a cell reference. For example, instead of typing =IF(A2<”05/07/2021”,”done”,”not done”), you can store the date
*05/07/2021*in a cell, say B2 and type the formula: =IF(A2<B2,”done”,”not done”).

- Alternatively, you can use the DATEVALUE function as explained in the first part of this tutorial.

- Another neat technique that you can use is to simply add a zero to the date (which has been enclosed in double quotes). So you can type: =IF(A2<”05/07/2021”+0,”done”,”not done”). This will make Excel take the date inside double quotes as a serial number, and use it in the logical test without having its value changed.

In this tutorial, I showed you some great techniques to use the IF function with dates. I hope the tips covered here were useful for you.

**Other articles you may also like:**

- Multiple If Statements in Excel (Nested Ifs, AND/OR) with Examples
- How to use Excel If Statement with Multiple Conditions Range [AND/OR]
- How to Convert Month Number to Month Name in Excel
- Why are Dates Shown as Hashtags in Excel? Easy Fix!
- How to Convert Serial Numbers to Date in Excel
- How to Get the First Day Of The Month In Excel?

Hi, can you identify the error here? “=If(G4=DATAVALUE(“1/0/1900″),”Email”,”No Email”)

Why isn’t this formula returning “Email”? Do I have to refer to the empty space (1/0/1900) as something else?

you have a typo. instead of “DATAVALUE” it should be “DATEVALUE”

What formula can I use to highlight a date that will expire soon but with a color, for example first aid course that will need renewing in 3 years time? thank you

No doubt there are other ways, but perhaps this will help…an example.

I have the following course Expiration in 4 Columns:

Course | Certification Date | Years | Renewal Date

Under the headers, the columns (cells from left to right) are formatted:

Column (A) = First Aid (formatted General or Text)

Column (B) = 3/1/2023 (formatted Date)

Column (C) = 3 (formatted General)

Column (D) = formula =DATE(YEAR(B2)+C2,MONTH(B2),DAY(B2))

Result = 3/1/2026 (formatted Date)

Column D is conditionally formatted:

Go To Menu – Conditional Formatting.

Select Highlight Cell Rules

More Rules…

>Format only cells that contain

Cell Value | less than | =NOW()+30

Select the Format button, select the Format Tab, and choose a color.

Press Ok I chose yellow as a warning.

If you want to get creative you can select Fill Effects, etc.

To test the functionality, change the date in Column B from 3/1/23 to 3/1/20. The cell in Column should change to yellow. If you want a longer lead time for the alert, change the formula from +30 to a higher number in the Conditional Formatting.

I hope this helps!

how would i write a formula to calculate the following…

x arrived into storage on a date

x left storage on a date

how many days within the invoice period of a week was x in storage

Do you want just to know how many days are in between the arrival and leaving date? If yes, you can do a simple subtraction (Leaving Date – Arriving Date).

Hi, what formula should I use to determine what answer between subtraction or sum?

Ex: I have a total $100.00, but depend of the situation i need to subtract or sum.

Would be really appreciate if you can help me, Thanks!!!

I have a cell that displays the current date using the NOW function. I need to multiply a column of values by 3% when the NOW function date is 01/01/2024. =IF(S37=DATEVALUE(“01/01/2024”), (U3*1.03), (U3))

This IF function works as long as the date is manually entered, but fails if NOW is used to determine the date.

Hi,

Question: If I enter today’s up 22/06/2023 in cell B2. In cell B3 I want the date range. Date Range = 19/06/2023-23/06/2023.

I need a IF THEN formula that will check to see if a cell has a date (any date). If the cell has a date in the field, I need the formula to show a 1 if there is a date and a 0 if there is no date. Is this type of formula something that can be done?

How do I write a formula that states: If today is past a certain day of the month, then that day of the next month, if not, that day of current month.

I need to know how to make a statement that would do the following. I have a column that lists the publication date as year only, i.e., 2018. I need to know within the column on the spreadsheet anything that is over 2022 and I need it to put that year in the column. Any advice is appreciated.

I have several things I am trying to do in which I can get it to work, but I want the result to be that a cell is blank if a date value isnt ented in another cell or its blank.

Basically, I have Cell D for an estimated date or a date value I would manually enter. Cell E for a date value to calculate Today-entered date value and if value in f is completed then e is blank.

As follows: =IF(F4=”Pending”,TODAY()-D4,IF(F4=”Completed”,””))

is it possible to add into that, that if D# is blank, then E# remains blank? Currently, if D is blank I get a value of 45231 days overdue which I would prefer no value display if there is no date.

I have tried to add the IF blank statement but it seems to bet ignored. Hopefully i am making sense and not just rambling.

Thanks in advance.

May something like =IF(D4=””,””,IF(F4=”Pending”,TODAY()-D4,IF(F4=”Completed”,””)))

Does a =IF formula exist to do the following:

When D3=Complete(determined by=ifs formula), i want todays date automatically displayed as completion date in D6

I hope this makes sense?

Your assistance will be appreciated

I would like to create an IF statement and the logical test be => referenced to real calendar date. =IF(=>06/26/2024),J23,-J23). This would be on or after 6/26/2024 the number is positive and before the number would be negative. What is the best method to refer to the date on the computer?

I am trying to create a flag for stock coming into the under 2 conditions ; Supplier and date. Does not seem to work. Can you provide any advice?

=IF(AND(‘Stock Management’!C:C=A2,’Stock Management’!K:K,C2),FALSE)

This ALWAYS returns a “FALSE”

Hi Steve,

Great tutorials. This should be easy but I’m tripping up with the formulas. There are several ways to do this, but can’t get it.

Objective: Need to derive the correct value for the “Permit Expiration Date” cell

Formula for the cell labeled “Permit Expiration Date”:

IF Last Inspection Date is blank, leave blank, IF Last Inspection Date is entered, add 170 days to the entered date, IF AHJ Permit Approve Date is blank, leave blank, IF AHJ Permit Approve Date is “TBD”, leave blank, else add 170 days to entered date.

My erroneous Example:

=IF(R4=””,””, IF(R4=Date, Date+170, IF(K4=””,””, IF(K4=”TBD”,””, IF(K4= Date, Date+170))))

Also, how do you protect a cell with a formula in it, and still allow users to enter dates in that cell?

I tried protecting the cell and worksheet so formulas can not be deleted but then the user can not enter dates in these cells.

Thank you kindly,

Jeff

I to match x2 different cells and then give me the date in a different cell. ie. the data “watch” is in cell B5, the matching “watch” is in B50,

the date for the data is in cell H5, I want the date to show up in cell B 50.