Johna needs to determine if a particular date is between two other dates. For instance, she may need to determine if November 8, 2013, is between August 1, 2013, and July 31, 2014. 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 interger 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, 2013) in cell A1 and your ending date (July 31, 2014) 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. 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")

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.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (12742) applies to Microsoft Excel 2007, 2010, and 2013.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

When working with dates in a worksheet, you may need to do some rather esoteric calculations on those dates. This tip looks ...

Discover MoreDates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, ...

Discover MoreWhen working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several ...

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

2017-03-31 12:30:20

ryan

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

2017-03-08 23:59:07

Chris

2017-02-13 21:28:22

TcheQ

2016-07-22 20:12:04

Mark

=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

2016-03-16 13:24:36

Bill R

Thanks a bunch - I appreciate it!

2016-03-15 15:28:39

allen@sharonparq.com

=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

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

2016-02-13 06:38:02

Michael (Micky) Avidan

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

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

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

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

.

Please advise

2015-03-26 06:31:10

Zubair

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

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

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

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.

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

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments