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

Excel IF Function Overview

• Description

The IF function tests a condition, then returns one of the two values depending on whether the test is TRUE or FALSE.

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

Example 1 – Comparing Between Two Dates Using If Formula

Case 1.1 – When Both Dates are Present in Cells

We have a list of products with their delivery date and deadline. We will determine whether the delivery is On Time or Delayed.

• Select cell E7.
• Insert the following formula:

• Press Enter.

• Drag the Fill Handle tool to cell E10.

• We will get the final delivery status of all the products.

Read More: How to Copy Same Date in Excel

Case 1.2 – While One Date Is Stored in the Formula

The only date we have is the delivery date. The deadline for the delivery is 1-20-22 and that value is put in cell F8.

• Select cell D5.
• Insert the following formula:

• Press Enter.

• Drag the Fill Handle tool down to cell D10.

• Here’s the result.

Example 2 – Using the IF Formula with the DATE Function

We will input the delivery status of the products in the ‘Status’ column.

• Select cell D5.
• Use the following formula in the cell:

• Hit the Enter key.

• Drag the Fill Handle to cell D10.

• Here’s the result.

How Does the Formula Work?

• DATE(2022,1,14): Makes a date out of the year, month, and day numbers. This is a static, manually-inserted value.
• 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

Example 3 – Combining the DATEVALUE Function in IF Formula with Dates

• Select cell D5.
• Insert the following formula:

• Pess Enter.

• Drag the Fill Handle tool.

• We will get the delivery status for all the products in the ‘Status’ column.

How Does the Formula Work?

• DATEVALUE(“18/01/2022”): The formula converts the text into a date with dd/mm/yyyy formatting.
• 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.

Example 4 – Applying AND Logic in the IF Formula with Dates

We will follow our previous dataset with a range of deadline.

• Select cell D5.
• Insert the following formula:

• Press Enter.

• Drag down the Fill Handle tool.

• 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: C5>=G8 and C5<=G9. The ‘$’ sign keeps the cell references fixed. The AND function yields TRUE only if both of the conditions are TRUE.
• 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.

Example 5 – Inserting TODAY in IF

Consider the deadline for delivery as today’s date 1-11-22. For you, it will be the date on which you are practicing. We will figure out the delivery status of all the products with the following steps:

• Select cell D5.
• Input the following formula:

• Hit the Enter button.

• Drag the Fill Handle tool to the next cells.

• Here’s our result.

How Does the Formula Work?

• TODAY(): Returns today’s date as a date value.
• IF(C5<=TODAY(),”On Time”,”Delayed”): Returns ‘On Time’ If the condition is TRUE otherwise give ‘Delayed’ as output.

Example 6 – Calculating Future or Past Dates in Excel Using the IF Formula

We’ll check whether the delivery will take place within ten days. Let’s see how we can do this:

• Select cell D5.
• Insert the following formula there:

• Press Enter.

• Drag the Fill Handle tool.

• We can see the delivery status for all the products in the ‘Status’ column of the dataset.

How Does the Formula Work?

• TODAY()+10: Returns the date ten days after today.

• 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 the Practice Workbook

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