How to Use IF Formula for Date Range in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

You can use the IF function to create an IF formula for working with the date range in Excel. In this article, we’re going to show you how to use  IF formula for date range in Excel.
We’ve taken a sample dataset with 3 columns: Products, Shipping Date, and Status. We’ll check if a date falls between a range, equals a range, and a few other criteria.

IF Formula for Date Range in Excel


1. Creating Formula with IF Function Only for Date Range in Excel

For the first method, we’re going to use the IF function to create a formula for the date range. A company just announced that they have shipped the products for the dates mentioned in the Dates column. We’ll check if our Shipping Date is equal to the Dates range.

Steps:

  • Firstly, type the following formula in cell D5.
=IF(C5=$F$5,"Shipped",IF(C5=$F$6,"Shipped",IF(C5=$F$7,"Shippped",IF(C5=$F$8,"Shipped",IF(C5=$F$9,"Shipped",IF(C5=$F$10,"Shipped",""))))))

This formula is checking if the value of cell C5 equals any of the values from the Dates range. If any match is found, then it will output “Shipped”, else it will leave the cell blank.

Moreover, remember to use the absolute cell reference.

IF Formula for Date Range in Excel

  • Secondly, press ENTER.

After that, we can see there is no match found. Therefore, it will output a blank cell.

  • Finally, use the Fill Handle to use that formula to cells.

Now, the cells C6 and C7 matched with the Dates range. Therefore, the status is “Shipped”.

IF Formula for Date Range in Excel

Read More: How to Pull Data from a Date Range in Excel


2. Applying AND & IF Formula for Date Range in Excel

In addition to the IF function, we’re going to use the AND function here for the date range. The dates between 10 March and 22 March will set the product Status “Shipped”.

Steps:

  • Firstly, type this formula into cell D5.
=IF(AND(C5>=$F$8,C5<=$G$8),"Shipped","Pending")

This formula is checking the date from cell C5 with cells F8 and G8. If the value is in between the range then it will show“Shipped”, else “Pending” will be shown. Again, remember to use the absolute cell references.

We’re using the AND function along with the IF function to use multiple criteria. The AND function is used when two or more criterion needs to be TRUE. If only one criterion is TRUE, then the AND function will return FALSE as the final output. We’re using the AND function to ensure more than one criteria is TRUE. Our date range has a lower and upper limit. We’re restricting it using the AND function.

IF Formula for Date Range in Excel

In conclusion, our dataset will show 4 products’ status as Shipped as it is within our date range.

IF Formula for Date Range in Excel


3. Combining Excel OR & IF Functions for Date Range

In this method, we’re going to use the OR function with the IF function to create a formula for the date range in Excel. In this, suppose the retailer is working on an alternative day. Hence, the Shipping Date falls on the Holidays, the Status will show “Will be Delayed”.

Steps:

  • Firstly, type the following formula in cell D5.
=IF(OR(C5=$F$5,C5=$F$6,C5=$F$7,C5=$F$8,C5=$F$9,C5=$F$10),"Will be Delayed","")

This formula is checking if our date from cell C5 equals any of the values from the Holidays date range. Our date in cell C5 is March 12. At first, the formula is checking if it is equal to the first value of the Holidays column, i.e. March 10. It will return FALSE, then it will move down to cell F6 and match. The next value is March 12, which is an exact match with our value from cell C5. If it finds no match this operation will continue till cell F10. That’s how our formula works.

Don’t forget to use absolute cell references.

IF Formula for Date Range in Excel

  • Secondly, press ENTER.

12 March is found in the Holidays range. Hence, the output is “Will be Delayed”.

  • Finally, AutoFill the formula to the rest of the cells.

Thus, we’ve made an IF formula using the IF and OR functions for the date range in Excel.

IF Formula for Date Range in Excel

Read More: How to Calculate Average If within Date Range in Excel


4. Combining Excel Functions to Create Formula for Date Range

We’re going to use the COUNTIF function alongside the IF function in this method to create an IF formula for the date range. We’re going to check if our products are shipped or not while matching them with the dates of Shipped column.

Steps:

  • Firstly, type the formula from below to cell D5.
=IF(COUNTIF($F$5:$F$10,C5),"Shipped","")

Our formula is checking whether the date from cell C5 is in the Shipped column range. The COUNTIF function will give 1 if it is available. 1 means TRUE. After that, our IF function will work and will show “Shipped” for 1 and blank (“”) for 0.

IF Formula for Date Range in Excel

  • Secondly, press ENTER.

Don’t forget to lock the date range using the absolute cell reference.

  • Finally, AutoFill the formula to cell C6:C10 range.

This is what the final step should look like. We’ve used yet another IF formula for a date range.

IF Formula for Date Range in Excel

Read More: How to Find Max Date in Range with Criteria in Excel


5. Creating Formula for Date Range with IF and TODAY Functions

In addition to the IF function, the TODAY function will be used here to create an IF formula for a date range. Here, the criteria will be if the Shipping Date values are less or equal to today’s date then it will output “Shipped”, else “Pending”. Let’s jump into the action.

Steps:

  • First, type the formula from below to cell D5.
=IF(C5<=TODAY(),"Shipped","Pending")

Today is March 23, 2022. We’re checking if the value from cell C5 is less or equal to March 23. If it is, then “Shipped” will be the output in cell D5.

IF Formula for Date Range in Excel

  • Then, press ENTER.

Our date is March 12, which is less than March 23, 2022. Therefore, we’ve got the value “Shipped”.

  • At last, AutoFill the formula to cell range C6:C10.

Finally, we can see that the values from the cell range C5:C8 are less than today’s date.

IF Formula for Date Range in Excel


6. Joining Excel IF-SORT Formula to Check the Date Range Order

This time, our goal is to see if a range of dates is in ascending order or not. We’re going to use the SORT function to create an IF formula for a date range.

Steps:

  • Type the following formula in cell D5.
=IF(SUM(--(C5:C10<>SORT(C5:C10,1,1,0)))=0,"YES","NO")

Formula Breakdown

We’ve several parts in our formula.

  • SORT(C5:C10,1,1,0) this portion is sorting the row range C5:C10 in ascending order.
  • C5:C10<>SORT(C5:C10,1,1,0) is comparing the cell values with the sorted values.
  • SUM(–(C5:C10<>SORT(C5:C10,1,1,0)) —> becomes
    • Output: {0}.

Hence, our formula will reduce to-

  • IF(TRUE,”YES”,”NO”).
    • Output: YES.

IF Formula for Date Range in Excel

  • Press ENTER.

The dates are in ascending order. Therefore, we’ve got “YES” as the output.

To check, if our formula works, we’ve changed a date. Thus, we’ve got “NO” as output.

IF Formula for Date Range in Excel


Download Practice Workbook


Conclusion

We’ve shown you 6 methods to use the IF formula for a date range in Excel. If you’ve any problems regarding these, feel free to comment below. Thanks for reading, keep excelling!


Related Articles


<< Go Back to Date Range | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo