How to Use If Function With Dates in Excel

You can often find yourself in a situation where you have certain dates in your data set and you want to compare them with one another, or with some certain value.

To achieve this, we will combine our date values with the IF function.

If Function With Dates

For our example, we will create a table of different loans, with just two pieces of information: the start date of the loan and the end date of the loan.

Now, let us say we want to know if any of these loans will be repaid by the end of 2027. Our reference value, in this case, is the 1st of January 2028.

We will place this value in cell G2.

When we have the IF function we always must have at least three criteria:

• Logical test. In our case, this is to check whether the values in our B column are smaller than our reference value.
• Value if true. What will be the value of a cell with our results if our logical test is true?
• Value if false. What will be the value of a cell with our results if our logical test is false?

For our example, we will create an IF function in the C column.

For the second row, our formula will be:

We will then drag and drop our formula in all of the remaining rows in the C column.

Since only the date in cell B2 is shorter than our reference date, only that loan will be repaid by the end of 2027.

Notice that we have also added the “=” sign, to return the YES value if our date in column B is of the same value as our reference date.  

Using the Datevalue Function

There is also one more option in store for us in case we do not want to use the reference value in a specific cell.

For this case, we can use the DATEVALUE function.  

Let us suppose that we want to find out if we have a good or bad loan. Our test is based on the simple fact that if the loan will expire before the end of 2029, then it is a good loan. Otherwise, we call it a bad loan.

It is the same thing as we did in the first example. The only difference is we do not have a reference value in a separate cell. Rather than that, we use the DATEVALUE function and input our data in the D column.

Our function in the D2 cell looks like this:

DATEVALUE function only has one parameter, and that is date_text. We have to input our date as a text. To do so, this text has to be in quotation marks and in the form of the date.

This function replaces our reference cell in the first example. After we typed the formula in the first row, we just dragged it to the bottom of our table, as seen in the table above.

Simple Comparison of Two Dates

Although it is not very useful in this example, there is, of course, a way to use the IF function for a simple comparison of two dates.

We know that in our case, the end date of a loan is always “larger” than the start date, but to confirm that, the IF function can be used. We will compare these two dates in the E column. 

Our formula in E2 cell is going to be as follows:

We will logically have all YES answers in the E column.