How to Use IF Formula with Dates (6 Easy Examples)

While working in Microsoft Excel, there are various formulas to make our work easier. IF formula is one of them. It has a wide range of applications in Excel. The IF function performs a logical test. It returns one value if the result is TRUE, and another if the result is FALSE. In this article, we will illustrate how to use the IF formula with dates. In order to do this, we will go over several examples.

Excel IF Function Overview

• Description

The IF function does nothing but test for a specific condition.

• Generic Syntax

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

• Argument Description

• Returns

The value we supply for TRUE or FALSE.

• Available in

All versions after Excel 2003.

1. Comparing Between Two Dates Using If Formula

First and foremost, we will use the IF formula to compare between two dates. At the time of doing this, there can be the following two scenarios.

1.1 When Both Dates are Present in Cells

In this case, both dates are present in cells which we have to compare. In the following dataset,  we have a list of products with their delivery date and deadline. We will calculate the status of the delivery whether the delivery is ‘On Time’ or ‘Delayed’. Let’s see how we can do this:

• Firstly, select cell E7.
• Now, insert the following formula:

• Press Enter.
• So, we can see the delivery status of the product mouse is ‘On Time’.

• Next, drag the Fill Handle tool to cell E10.

• Finally, we will get the final delivery status of all the products.

Read More: How to Copy Same Date in Excel

1.2 While One Date is Stored in the Formula

Sometimes we will have a dataset like the one given below. Here, the only date we have is the delivery date. The deadline for the delivery is 1-20-22. Let’s figure out the delivery status in the ‘Status’ column of the dataset.

• In the beginning, select cell D5.
• Insert the following formula:

• Now, press Enter.
• Here, we can see the delivery status is ‘On Time’ for the product mouse.

• After that, drag the Fill Handle tool downwards to cell D10.

• Finally, The delivery status of the dataset looks like this.

Read More: How to Add Time in Excel Automatically

2. Using IF Formula and DATE Function at The Same Time

In this example, we will use the IF formula and the DATE function together. Like the previous dataset, we will input the delivery status of the products in the ‘Status’ column. Follow the simple steps with us to perform this:

• First, select cell D5.
• Type the following formula in that cell:

• Hit the Enter key.
• So, we get the delivery status of the product mouse as ‘On Time’.

• Now, drag the Fill Handle to cell D10.

• As a result, we get the delivery status of all the products in the ‘Status’ column.

🔎 How Does the Formula Work?

• DATE(2022,1,14): Takes the date to compare.  
• IF(C5<=DATE(2022,1,14),”On Time”,”Delayed”): Returns the value of the delivery status.

Read More: How to Calculate Due Date with Formula in Excel

3. Combining Excel DATEVALUE Function in IF Formula with Dates

In Excel, the DATEVALUE function converts date into text. We can merge this function with the IF formula to calculate dates. For this example, we will go with our previous dataset with a different deadline. Just follow the below instructions in order to do this:

• Firstly, select cell D5.
• Put the following formula there:

• Then, press Enter.
• Here, we can see the delivery status of the first product mouse is ‘On Time’.

• After that, drag the Fill Handle tool.

• Finally, we will get the delivery status for all the products in the ‘Status’ column like the below figure.

🔎 How Does the Formula Work?

• DATEVALUE(“18/01/2022”): Consider the date 18/01/22.  

• IF(C5<=DATEVALUE(“18/01/2022″),”On Time”,”Delayed”): Returns the value of the delivery status ‘On Time’ if the condition is TRUE. Otherwise gives ‘Delayed’ as output.

Read More: How to Use Excel Date Shortcut

4. Applying AND Logic & IF Formula with Dates in Excel

Using AND logic along with the IF formula, we can calculate dates in excel. The AND logic returns an output where all the conditions need to be TRUE or FALSE. We will follow our previous dataset with a range of deadlines. Let’s see how can we do this:

• In the beginning, select cell D5.
• Insert the following formula:

• Now, press Enter.
• So, we get the delivery status of the product mouse with AND logic.

• Then drag down the Fill Handle tool.

• As a result, we get the delivery status for all the products in the ‘Status’ column of the dataset.

🔎 How Does the Formula Work?

• AND(C5>=$G$8,C5<=$G$9): This part represents two conditions. One is C5>=G8 and another is C5<=G9. ‘$’ sign keeps the cell reference fixed.
• IF(AND(C5>=$G$8,C5<=$G$9),”On Time”,”Not In Time”): If the condition is TRUE, return the value ‘On Time’. Otherwise gives ‘Delayed’ as output.

5. Inserting TODAY & IF Formulas with Dates

A combination of the TODAY function and IF formula is another approach to counting dates in Excel. Suppose, we have a dataset of products along with their delivery date. Let us consider the deadline for delivery as today’s date 1-11-22. For you, it will be the date on which you are practicing. Now we will figure out the delivery status of all the products with the following steps:

• First, select cell D5.
• Input the following formula:

• Hit the Enter button.
• Here, in cell D5 we get the delivery status for the product mouse in ‘On Time’.

• Next, drag the Fill Handle tool to the next cells.

• Finally, the delivery status for all the products looks like the below figure.

🔎 How Does the Formula Work?

• TODAY(): This part takes the date of the present day.
• IF(C5<=TODAY(),”On Time”,”Delayed”): Returns ‘On Time’ If the condition is TRUE otherwise give ‘Delayed’ as output.

6. Calculating Future or Past Dates in Excel Using IF Formula

In this example, we will check if a date is in a range or not. For example, take today’s day into account. The motive of this example is to check whether the delivery will take place or not within ten days. Let’s see how we can do this:

• Firstly, select cell D5.
• Type the following formula there:

• Then, press Enter.
• Now we can see that the delivery status of the product mouse is within range. The delivery will take place within 10 days from today.

• After that, drag the Fill Handle tool.

• In the end, we can see the delivery status for all the products in the ‘Status’ column of the dataset.

🔎 How Does the Formula Work?

• TODAY()+10: Takes the date after ten days from the present date.

• IF(C5<TODAY()+10,”Within range”,”Out of range”): If the condition is TRUE returns ‘Within Range’ otherwise gives ‘Out of range’ as output.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In this article, we have covered calculating the dates using the IF formula. Hopefully, the above examples will help you to understand the logic of the IF formula with dates. Download the practice workbook added with this article and practice yourself. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible.

Further Readings

• How to Enter Time in Excel
• How to Add Military Time in Excel
• How to Create World Time Zone Clock in Excel
• Making a List of Countries by Time Zone in Excel

<< Go Back to Excel IF Function | Excel Functions | Learn Excel