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

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.

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.

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.

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

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.

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.

• Secondly, press ENTER.
• Finally, drag down the Fill Handle to AutoFill the formula.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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

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

• VLOOKUP Date Range and Return Value in Excel
• How to Use Formula for Past Due Date in Excel