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.


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:

When Both Dates are Present in Cells

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

When Both Dates are Present in Cells

When Both Dates are Present in Cells

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

While One date is Stored in the Formula

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

While One date is Stored in the Formula

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

While One date is Stored in the Formula

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

Use of IF Formula and DATE Function At The Same Time

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

Use of IF Formula and DATE Function At The Same Time

  • Now, drag the Fill Handle to cell D10.

Use of IF Formula and DATE Function At The Same Time

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

Excel DATEVALUE Function Wrapped in IF Formula with Dates

  • After that, drag the Fill Handle tool.

Excel DATEVALUE Function Wrapped in IF Formula with Dates

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

Apply AND Logic & IF Formula with Dates in Excel

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

Apply AND Logic & IF Formula with Dates in Excel

  • Then drag down the Fill Handle tool.

Apply AND Logic & IF Formula with Dates in Excel

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

Insert TODAY & IF Formulas with Dates

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

Insert TODAY & IF Formulas with Dates

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

Insert TODAY & IF Formulas with Dates

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

Calculate Future or Past Dates in Excel Using IF Formula

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

Calculate Future or Past Dates in Excel Using IF Formula

  • After that, drag the Fill Handle tool.

Calculate Future or Past Dates in Excel Using IF Formula

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

Mukesh Dipto

Hello, everyone. Myself Mukesh Saha Dipto. I completed my studies in Engineering. I am currently employed with SOFTEKO as a technical content writer. Working in a digital platform and learning about the market has always been a dream of mine. So, I have chosen to work here to explore in a new environment. As a result, I've chosen to work here in order to get experience in a new setting. Sports, travel, and meeting new people are some of my favorite pastimes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo