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.

## Download Practice Workbook

You can download the practice workbook from here.

**Overview of Excel IF Function**

**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**

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

**6 Uses of IF Formula with Dates in Excel**

**1. Compare 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:

`=IF(D5>=C5,"On Time","Delayed")`

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

**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:

`=IF(D5>=C5,"On Time","Delayed")`

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

**2. 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:

`=IF(C5<=DATE(2022,1,14),"On Time","Delayed")`

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

**3. Excel DATEVALUE Function Wrapped in IF Formula with Dates**

In excel** 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 instruction in order to do this:

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

`=IF(C5<=DATEVALUE("18/01/2022"),"On Time","Delayed")`

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

**4. Apply 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:

`=IF(AND(C5>=$G$8,C5<=$G$9),"On Time","Not In Time")`

- 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,**returns the value**‘On Time’.**Otherwise gives**‘Delayed’**as output.

**5. Insert TODAY & IF Formulas with Dates**

A combination of the **TODAY function **and **IF formula** is another approach to count dates in excel. Suppose, we have a dataset of products along with their delivery date. Let us consider the deadline for delivery is 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:

`=IF(C5<=TODAY(),"On Time","Delayed")`

- 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. Calculate 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:

`=IF(C5<TODAY()+10,"Within range","Out of range")`

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

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