# 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
ARGUMENT REQUIREMENT DESCRIPTION
logical_test Required This is the condition that will be tested and rated as TRUE or FALSE.
[value_if_true] Optional When a logical test evaluates to TRUE, this is the value to return.
[value_if_false] Optional When a logical test evaluates to FALSE, this is the value to return.
• 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:
`=IF(D5>=C5,"On Time","Delayed")`
• Press Enter.

• 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:
`=IF(C5<=\$F\$8+0,"On Time","Delayed")`
• 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:
`=IF(C5<=DATE(2022,1,14),"On Time","Delayed")`
• 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.

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

• Select cell D5.
• Insert the following formula:
`=IF(C5<=DATEVALUE("18/01/2022"),"On Time","Delayed")`
• 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

• Select cell D5.
• Insert the following formula:
`=IF(AND(C5>=\$G\$8,C5<=\$G\$9),"On Time","Not In Time")`
• 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:
`=IF(C5<=TODAY(),"On Time","Delayed")`
• 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:
`=IF(C5<TODAY()+10,"Within range","Out of range")`
• 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.

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

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF