# Determining If a Date is between Other Dates

by Allen Wyatt
(last updated January 28, 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:

```=IF(AND(D1>=A1,D1<=A2),"Yes","No")
```

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(AND(TRUNC(D1,0)>=TRUNC(A1,0),TRUNC(D1,0)<=TRUNC(A2,0)),"Yes","No")
```

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:

```=IF(AND(DATEVALUE(D1)>=DATEVALUE(A1),DATEVALUE(D1)<=DATEVALUE(A2)),"Yes","No")
```

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:

```=IF(D1=MEDIAN(D1,A1,A2),"Yes","No")
```

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:

```=IF(A3=MEDIAN(A1:A3),"Yes","No")
```

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.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

When working with large amounts of data, it is a good idea to make sure that the data all consistently follows a pattern. ...

Discover More

Replacing All Building Blocks

Word's Building Blocks can be a great tool to improve your productivity when writing. Depending on the type of writing ...

Discover More

Highlighting Duplicate Words

One way to help improve your writing is to minimize the number of duplicated words you use in your prose. Depending on ...

Discover More

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!

##### More ExcelTips (ribbon)

Converting UNIX Date/Time Stamps

If you import information generated on a UNIX system, you may need to figure out how to change the date/time stamps to ...

Discover More

Determining Contract Weeks

Everyone seems to determine the difference between dates differently. Nicole has a need to calculate contact weeks (the ...

Discover More

There are calendar days and then there are business days. Excel provides two functions (NETWORKDAYS and NETWORKDAYS.INTL) ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 4?

2019-03-06 07:36:29

SteveJez

@James,
That formula will not work for what you are trying to do. The following will work though if I have understood the layout of your data correctly

=SUMPRODUCT(--(Sales_Grp=\$A\$3),--(Status=\$B\$4),--(Order_month>=\$G\$1),--(Order_month<=\$G\$2),Net_value)

if the layout is not right message me.
(see Figure 1 below)

Figure 1. assumed data layout

2019-03-06 03:57:31

SteveJez

@James,
Using your references, delete Criteria Range 4 & Criteria 4 & use the following as Criteria 3

AND(Order_month>G1,Order_month<G2)

HTH

Steve

2019-03-05 13:36:25

James Jenkinson

Can someone help me with this function, in particular the date criteria, where Criteria_range3 and 4 ('Order_month') is a column with dates in the format dd/mm/yyyy and Criteria3 and Criteria4 are date fields in the following arrangement

To report data in Jan-19
G1 = 31/12/2018
G2 = 01/02/2019

=SUMIFS(Net_value,Sales_Grp,\$A\$3,Status,\$B4,Order_month,">G\$1",Order_month,"<G\$2")?

Can I combine > and < in one expression to report data in the required date range?

2018-09-17 07:35:47

Deepak

I am trying to put AL in front of id nos. if the dates falls between those days then formula print AL / SL .....

2018-07-18 18:24:25

Karen

I'm trying to write an IF statement for returning a value if a cell date is between a series of months, but the year doesn't matter. Is there a way to do this? I am trying to avoid having to update the formula every year as we have certain criteria that have to change between Sept and Feb every year.

Basically, if the date in cell A1 is a Sept thur Feb date, it would be TRUE, otherwise FALSE.

2018-05-18 07:59:12

Michael (Micky) Avidan

@Rejive,
Check out my suggestion in the following picture.
(see Figure 1 below)
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

Figure 1.

2018-01-28 03:54:13

Rejive

Hi
I need one help to compute the below in excel sheet
If Date and Time 7/01/2018 06:23:54 is between 6am to 14pm it should show 07/01/2018 A Shift.
If Date and Time 7/01/2018 16:23:54 is between 14pm to 22pm it should show 07/01/2018 B shift.
If Date and Time 8/01/2018 04:23:54 is between 22pm to 06am of Next Day it should show 07/01/2018 C shift.

Regards
K.Rejive
rejive_k@yahoo.com

2017-03-31 12:30:20

ryan

hi, I'm having a problem in using the function ("datevalue"), and i really need a help about this formula: "if(and(datevalue(A)≥B,datevalue(A)≤C),D/E,0)“
example:
A = dec. 30, 2017
B = dec. 30, 2017
C = jan. 15, 2018
D = 1, E = 1, so D/E = 1

looking at the formula, it should give an answer of 1. but it give an answer of 0 in excel.
using this formula in dates with the same year is easy, using this formula in dates with different year really is confusing because it always gives me a result of 0.

thank you and hope you can help me solve this problem. looking forward for a solution.

2017-03-20 12:00:47

Daniel

I have two dates. I need to determine if either date is between another range of dates. If either date falls between that fixed date range, I want to return a value of "1". If not, "0". Is that an =IF(OR( formula? what would that look like?

2017-03-08 23:59:07

Chris

Is there a way to show a date range in one cell? then using that cell I already have a formula I would like to use to look at that cell and if the current date falls in that date range, to use a particular column of numbers and if it doesn't fall into that date range, it uses it's original column of numbers. The challenge I'm having is finding a format or formula perhaps where I don't have a separate start and end date in separate cells. Is this possible?

2017-02-13 21:28:22

TcheQ

Brilliant, Median function is what I was after as I'm already on a 6-variable nested-if line. Will be using this in future!

2016-07-22 20:12:04

Mark

I had a need to assign a bonus value based an employees hire date, against a a date range. Employees hired with the date range were eligible for a single-value bonus, those hired outside the date range were not. Your formula worked perfectly:
=IF(AND(\$C3>=\$A\$57,\$C3<=\$B\$57),"\$1,000.00","0")

Where:
C3=hire date
A57=start of date range
B57=end of date range

The bonus value was \$1K if the hire date was within range.
Thanks!!

2016-07-02 21:17:37

Donna

I am going to use the information you provided to ensure dates on my job applications do not conflict. It seem there could be a problem that I can not detect visually.

2016-03-16 13:24:36

Bill R

Thanks a bunch - I appreciate it!

2016-03-15 15:28:39

allen@sharonparq.com

Peter's answer is still a good one, Bill. You could use the following formula:

=TEXT(A1, "mmmm")

If you wanted, you could also simply use this formula:

=A1

All you would then need to do is to apply a custom format to the cell containing this formula so that it only shows the month.

2016-03-15 14:48:21

Bill R

Sorry - I guess I didn't state my question clearly enough. I have dates in a single column (A1:A50). I am attempting to create another column next to that, and if a date in A1 is >12/31/2015 and <02/01/2016, I would like to return a textual value of "January" in the newly created column.

2016-03-04 11:21:20

Peter Atherton

Bill R

With dates in A1:C1

=TEXT(MEDIAN(A1:C1),"mmmm")

2016-03-02 11:13:13

Bill R

But, how do you return a text value based on range that is NOT found in a cell in a worksheet? For example, I would like to simply return "January" if I (arbitrarily) have a range of >12/31/2015, and <02/01/2016.

2016-02-13 06:38:02

Michael (Micky) Avidan

Sorry for the mistake.
A8 should be A9 .
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2016-02-13 06:36:34

Michael (Micky) Avidan

@Doug Shaffer
You don't need to "go through" the Range titles.
You can directly calculate the "BONUS" (or whatever you calculate)
Take a look at the linked picture:
http://jpg.co.il/view/56bf144ba9d8f.png/
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2016-02-12 16:50:29

Doug Shaffer

I need a formula that identifies a number between 4 different ranges, and then depending on which range is identified, calculates a commission rate. I cannot figure it out!!!

For example,
If Cell# is greater than 0 but less than or equal to 40,000.00 then "Range 1";If Cell# is greater than or equal to 40,000.01 but less than 60000.00 then "Range 2"; If Cell# is greater than or equal to 60,000.01 but less than 100000.00 then "Range 3";If Cell# is greater than or equal to 100,000.01 then "Range 4"

Then,
If Cell#="Range 1" then "No Commission"; If Cell#="Range 2" then multiply Cell# by 1.5%; If Cell#="Range 3" then multiply Cell# by 1.75%;If Cell#="Range 4" then multiply Cell# by 2%.

2015-10-10 12:25:38

sameer

I want to if formula as below
Emp code with logging date A15432(5-6-2015)want to check with other excel emp code with two date A15432(1-6-2015) (10-6-2015)..
.

2015-03-26 06:31:10

Zubair

Hi,
How can i do vlookup, Hyperlink Excel 2007 kindly could you tell me how can i do i am very thank full to you

2014-12-11 14:57:00

Guy

Hello Allen, I have a spreadsheet issue that I am not able to solve.

I have roughly 7200 rows of raw data that I need to organize into a weekly schedule.

My data is both text and date/time formatted.

In column A I have cells that are repeated (roughly 1038 unique) and I need another column E with Date data to be compared to a date range, if the data in Column E is between the range (7 day range, where Jan1 to Jan7 is Week1 as a Column Header) True then I would like the Weekly Column Header to placed in the cell.
The final output should be the unique rows of column A and Columns 2014Week40 thru 2016Week13. Where the Week column header will be added to the unique equipment number in column A.

I can send the raw data but I can't attach in this forum.

Thanks,
Guy

2013-11-25 02:31:49

Jackie

You can also use vlookup, using the true function. So a table of dates with a "bucket" value afterwards, eg
01/01/2012 Last year
01/01/2013 This year
As Excel treats dates as numbers it will find the closest.

2013-11-23 10:09:31

Juan

It would be easer to understand if you put a screenshot of an example with data, because it's difficult to imagine how the data is arranged. Thanks for the tip!

##### This Site

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.