Working with dates is an important skill for anyone working with Excel. In this tutorial, you’ll learn how to use Excel to check if a date falls between two dates. This can be very helpful when you’re checking whether or not payments were made during a reporting period.
By the end of this tutorial, you’ll have learned:
- How to use an Excel IF() function to check if a date falls between two dates
- How to return TRUE or FALSE with a simpler IF() function if a date falls between two dates or not
How Excel Stores Dates
Excel has a clever way of storing date and time values. The date portion is stored as an integer value, with January 1, 1900, as the first date. Any date beyond that is incremented by 1. For example:
- January 3, 1900, would be equal to 3
- November 23, 2023, would be 45253
This is important because it allows you to understand how we can check if a date falls between two dates. Since dates increase sequentially, we can see if the number that represents a date is larger than the start date and smaller than the end date.
Using IF and AND Functions to Check if a Date Falls Between Two Dates in Excel
How can you check if a date is between two dates in Excel?
To check if a date is between two dates using Excel, you can use the formula: IF(AND(A2<end, A2>start), “Yes”, “No”). This checks if the date in cell A2 falls between the dates of start and end. It returns “Yes” if it is, otherwise “No”.
Let’s take a look at the formula used to calculate whether or not a date is between two dates:
=IF(AND(A2<end, A2>start), "Yes", "No")The function uses the AND() function which is used to check if both items in the function are true. If any of the items are not true, the function will return False. We wrap the entire AND() function in the IF() function, which is used to return a statement indicating whether the date is between two dates.
Let’s take a look at a step-by-step example of how to use this custom formula in Excel.
Step 1: Load Your Dataset in Excel
Open the dataset that you want to work with in Excel. In the sample dataset above, we have a dataset with five dates in column A. We’ll use column B to check whether or not the date in the first column is between two dates.
Step 2: Enter Your Start and End Dates Into Two Cells
Enter the start and end date into the cells E1 and E2, respectively. In this case, we’re using the dates of September 1, 2023 and November 1, 2023 as our start and end dates.
Step 3: Enter the IF() Function to Check if a Date is Between Two Dates in Excel
We enter our formula into cell B2 in order to check if the date in cell A2 falls between the start and end dates. In order to make our formula dynamic, we need to use absolute references for the values that hold our start and end dates.
Step 4: Use the Fill Handle to Check if All Dates Fall Between the Two Dates
When you click and drag down the fill handle, Excel copies the formula into all of the remaining cells. Since we used absolute references in our formula, the references continue to point to the right values.
In the following section, you’ll learn how to return either a True or False if a date falls between two dates in Excel using a simpler formula.
Return True or False if a Date is Between Two Dates in Excel
In the previous example, we returned a custom value if a date fell between two other dates. In some cases, however, you’ll only want to return a simple true or false. This can be helpful if you need to use the boolean value (the true or false) in another function.
This approach also makes our calculation significantly simpler. Because the AND() function returns a true or false, we don’t need to use the IF() function at all. Let’s see what this looks like in Excel.
Step 1: Load Your Dataset in Excel
Open the dataset that you want to work with in Excel. In the sample dataset above, we have a dataset with five dates in column A. We’ll use column B to check whether or not the date in the first column is between two dates.
Step 2: Enter Your Start and End Dates Into Two Cells
Enter the start and end date into the cells E1 and E2, respectively. In this case, we’re using the dates of September 1, 2023 and November 1, 2023 as our start and end dates.
Step 3: Enter the AND() Function to Check if a Date is Between Two Dates in Excel
We enter our formula into cell B2 in order to check if the date in cell A2 falls between the start and end dates. In order to make our formula dynamic, we need to use absolute references for the values that hold our start and end dates.
Step 4: Use the Fill Handle to Check if All Dates Fall Between the Two Dates
When you click and drag down the fill handle, Excel copies the formula into all of the remaining cells. Since we used absolute references in our formula, the references continue to point to the right values.
Conclusion
In this tutorial, you learned how to check if a date is between two dates in Excel. Because Excel stores dates as whole numbers, you can use simple, numeric functions to check if a date falls between two other dates. You first learned how to use the IF() and AND() functions to return a custom value if a date falls between two dates. Then, you learned how to simplify this process by returning only a true or false value. This was done by only using the AND() function, making the result easily usable in other functions.
Additional Resources
To learn more about related topics, check out the tutorials below: