How to Check if a Date is Between Two Dates in Excel?
Need to verify if a date lies within a specific range? This guide shows you how to use the IF function effectively.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Return a value if a date falls between a start date and end date (inclusive on both ends)"
=IF(AND(A1>=B1, A1<=C1), "In Range", "Out of Range")This formula checks if a date in cell A1 is between the dates in cells B1 and C1, returning 'In Range' if true, otherwise 'Out of Range'.
Step-by-step breakdown
- A1 is the cell containing the date you want to check.
- B1 is the start date of the range.
- C1 is the end date of the range.
- The AND function checks if both conditions (A1 >= B1 and A1 <= C1) are true.
- The IF function returns 'In Range' if the AND condition is met, otherwise it returns 'Out of Range'.
Edge cases & warnings
- The date in A1 is exactly equal to the start date in B1.
- The date in A1 is exactly equal to the end date in C1.
- The date in A1 is before the start date in B1.
- The date in A1 is after the end date in C1.
- The start date and end date are the same.
Examples
"A1: 2023-05-15, B1: 2023-05-10, C1: 2023-05-20"
In Range"A1: 2023-05-25, B1: 2023-05-10, C1: 2023-05-20"
Out of RangeFrequently asked questions
Can I use this formula for non-date values?
No, this formula is specifically designed for date comparisons.
What happens if the dates are in different formats?
Ensure all dates are in the same format to avoid errors.
Can I return a different value instead of 'In Range'?
Yes, you can customize the output in the IF function.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.