by Allen Wyatt
(last updated May 25, 2019)
Johna needs to determine if a particular date is between two other dates. For instance, she may need to determine if November 8, 2018, is between August 1, 2018, and July 31, 2019. She is trying to figure out the formula that will make this determination.
There are actually a wide range of formulas you could use, ranging from the short to the very long. In this tip, though, I'll focus on the shorter formulaic approaches. Before doing so, it is good to remember that dates (and times) are stored internally by Excel as numbers. The portion of the date before the decimal point (the integer portion of the number) is the serial number for the date, and the portion to the right of the decimal place is the time.
Since dates and times are stored as numbers, it becomes a relatively easy task to simply compare the numbers to determine which is higher or lower and if something is between the high and low. For instance, let's assume that you have your starting date (August 1, 2018) in cell A1 and your ending date (July 31, 2019) in cell A2. You could place the date to check into cell D1 and use the following formula:
The formula checks to see if the date in D1 is both greater than or equal to A1 and less than or equal to A2. Only if those two conditions are met is "Yes" returned, otherwise "No" is returned. (See Figure 1.)
Figure 1. Comparing numbers to determine which is higher or lower and between the high and low.
If you think that the dates include times, then you may want to adjust for that by stripping off the portion of the serial number that represents the time:
If you are actually comparing text strings and not recognized Excel dates, then you'll need to use the DATEVALUE function to convert the strings into dates:
The result of using the DATEVALUE function would be similar to the following figure: (See Figure 2.)
Figure 2. Comparing text strings using the DATEVALUE function to convert strings into dates.
Each of the formulas, so far, has been the same basic formula; the only thing different between them is the adding of additional functions to compensate for the characteristics of how the dates are stored in the cells. For an entirely different way to determine if a date is between two other dates, you could use the following formulaic approach:
Since the MEDIAN function is calculated using three numbers, it always returns the number that is between the lower and the higher number. This means that if D1 is really between the other two, it will always be returned; if D1 is not between the other two, then one of the others will be returned.
Interestingly, if you reorganized your data a bit so that the three values were adjacent to each other (for instance, by moving the comparison date from D1 to A3), then you could replace the three separate cell references with a range of the three cells, making the formula even shorter:
The advantage to using the MEDIAN approach to the AND approach is that you don't need to worry which of the range values (A1 or A2) is the start or the end date for the comparison—the MEDIAN function sorts that all out. (See Figure 3.)
Figure 3. Comparing values using the MEDIAN funtion.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12742) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you need to know the years in which a particular date occurred on a specific day of the week, there are a number of ...Discover More
Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based ...Discover More
Need to print an elapsed date in a strange format? It's easier to do than may appear at first glance. Here's a discussion ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.