Formula for matching a date within a date range
Table of contents
- Match a date when a date range is entered in a single cell
- Match a date when a date range is entered in two cells
- Use VLOOKUP to search date in date ranges and return value on same row
- Match a date when date ranges sometimes overlap and return multiple results
- Functions in this post
Match a date when a date range is entered in a single cell
Column C contains the start and end date separated by a forward slash /. The formula in cell C9 splits the dates and checks if the date in cell C8 is in a date range and if it is it returns the adjacent value on the same row.
Formula in C9:
Watch a video where I explain the formula above
Recommended articles
Find date range based on a date
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
Find date range based on a date
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
How to return a value if lookup value is in a range
How to create an array formula
- Select cell C9
- Click in formula bar
- Copy and paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys
Recommended article
Array formulas allows you to do advanced calculations not possible with regular formulas.
Download excel file, see sheet Ex 1
matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)
Match a date when a date range is entered in two cells
There are no gaps between these date ranges which makes it is possible to only use the dates in column C in the following formula:
Formula in cell C9:
Watch a video where I explain the formula above
Remember, the LOOKUP function requires date values to be sorted in an ascending order in column C. If not, use the formula below.
If you do have gaps between some date ranges, like the picture below. There is a date gap between 3-1-2009 and 4-1-2009. You then need to use both the start and end date to find the value you are looking for.
Formula in cell C9:
If the user enters a value outside the date ranges an error #N/A is returned.
Watch a video where I explain the formula above
Recommended article
La Thăng asks: I want to find day if given date and week, for example : if given Tuesday, 32th […]
How to enter an array formula
- Select cell C9
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter
Explaining formula in cell C9
Step 1 - Check if date is in any of the date ranges
($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)
becomes
(39994>={39814;39904;39995;40087})*(39994<={39903;39994;40086;40178})
becomes
{TRUE;TRUE;FALSE;FALSE}*{FALSE;TRUE;TRUE;TRUE}
and returns
{0;1;0;0}
Step 2 - Multiply with relative row numberÂ
($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)* MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))
becomes
{0;1;0;0}*MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))
becomes
{0;1;0;0}*{1;2;3;4}
and returns {0;2;0;0}
Step 3 - Sum values in array
SUMPRODUCT(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)* MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))
becomes
SUMPRODUCT({0;2;0;0})
and returns 2.
Step 4 - Return value from cell range
INDEX($B$3:$B$6, SUMPRODUCT(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)* MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))))
becomes
INDEX($B$3:$B$6, 2)
becomes
INDEX({"A";"B";"C";"D"}, 2)
and returns B in cell C9.
Use VLOOKUP to search date in date ranges and return value on the same row
The following formula uses only the VLOOKUP function, however, the dates must be sorted in an ascending order and if a date is outside a date ranges 0 (zero) is returned. There can't be any overlapping date ranges and the formula can only return one value.
The example below has date ranges in only one column.
You are also required to have the lookup column in the first column in the cell reference you use in the VLOOKUP function. Example, the second argument in the VLOOKUP function below is this cell reference:Â B3:C10. The lookup column must be in column B.
Formula in cell C13:
Important! The first date range seems to be 1-1-2009/3-31-2009 but it is actually 1-1-2009/3-30-2009, why is this? The VLOOKUP function matches the largest date that is smaller or equal to the lookup date. If the lookup date is 3-31-2009 it will match 3-31-2009 found in cell B4 and return the corresponding value in column C (cell C4). In this case nothing, cell C4 is empty. This applies to all date ranges in column B.
You need to change your date ranges accordingly if you want to use the VLOOKUP function for date ranges entered vertically. However, the VLOOKUP function works perfectly fine if you have date ranges with no gaps between the end dates and start dates, see picture below. You then only need to use the start dates for each date range, example demonstrated in column C see picture below.
Recommended article
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
How to return a value if lookup value is in a range
Download excel file, see sheet Ex 4
matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)
Match a date when date ranges sometimes overlap and return multiple results
Array formula in cell C9:
Recommended article
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
Identify overlapping date ranges
How to enter an array formula
- Select cell C9
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Select cell C9
- Copy cell (not formula)
- Select cell range C10:C11
- Paste
Download excel file, see sheet Ex 3
matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
FIND(find_text,within_text,[start_num])
Returns the starting position of one text string within another text string. FIND is case-sensitive
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
LEFT(text,num_chars)
Returns the specified number of characters from the start of textstring
RIGHT(text,num_chars)
Returns the specified number of characters from the end of textstring
ROW(reference)
Returns the rownumber of a reference
LEN(text)
Returns the number of characters in a text string
DATEVALUE(date_text)
Converts a date in the form of text to a number that represents the date in Microsoft Office Excel date-time code
Related articles
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Count dates inside a date range
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
48 Responses to “Formula for matching a date within a date range”
Leave a Reply to Oscar
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
This much shorter formula appears to work also...
=LOOKUP(C8,RIGHT(C$3:C$6,10)-91+(C8=--(YEAR(C8)&"-03-31")),B$3:B$6)
Very interesting! Thanks!
Hi,
What type of formula could be used if you weren't using a date range and your data was not concatenated?
ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 and Range2
Range1 Range2 Value
1.33 1.66 A
1.67 1.99 B
2.00 2.33 C
MT,
see this post: https://www.get-digital-help.com/2010/01/13/return-value-if-in-range-in-excel/
Hi!!
This is Ramki. Require your help to fix my concern.
Need a formula to return multiple values while using a double look up formula (Index/Match).
Hi! my date range is set up in two columns
A B C
Start_Date End_Date Item
1-1-12 2-1-12 Text_1
2-2-12 2-15-12 Text_2
I have another list where dates are in sequence and want to lookup text value for each day
A B
Date Item_result
1-1-12 Text_1
1-2-12 Text_1
Ahmed Ali,
I added new content to this post. See:
Match a date when a date range is entered in two cells
Can't thank you enough will give it a try
Again can't thank you enough it worked like a charm. the only thing is that when i search for a date that does not have a task it returns the last task. how can i avoid that. thanks
Hi,
Regarding the formula for "Match a date when date ranges sometimes overlap and return multiple results", is there a way to revise the formula to just look at the dates and indicate if there is crossover?
For example, if I have start date and end dates in four columns and start date in B1 is 1/1/10 and end date in B2 is 12/31/14 and start date in D2 is 10/1/13 and end date in E2 is 10/31/13, I'd like the formula to recognize that the 10/1/13-10/31/13 date range crosses over within the 1/1/10-12/31/14 date range.
Is this possible?
Thanks!
Brett,
I think this post is what you are looking for:
Find overlapping date ranges in excel
Thanks Oscar!
Oscar,
in 2010 excel this isnt working? im getting a #value! error?
I saved you worksheet and i click in c9 and hit enter and it returns the error, any idea whats going on?
anon,
Create an array formula! Instructions above!
Hi Oscar,
I need a fomula that gives me the number of days contained in a range that overlap anoter range... not sure if that is clear enough...
Rene,
read this post: Days contained in a range that overlap another range
[...] Rene asks: [...]
SIR, I AM TRYING TO FIND THE TOTAL OF QUANTITIES DURING GIVEN DATE RANGE IN EXCEL SHEET. SUPPOSE THE QUANTITIES ON EACH OF THE DAY FROM 1 TO 30 IS GIVEN, I NEED TO FIND SUM OF QUANTITIES DURING 7 TO 13. PLEASE HELP. THANKS. SRINIVAS
srinivas,
Hi! my date range is set up in two columns as ALI has
A B C
Start_Date End_Date Item
1-1-12 2-1-12 Text_1
2-2-12 2-15-12 Text_2
I have another list where dates are in sequence and want to lookup text value for each day
A B
Date Item_result
1-1-12 Text_1
1-2-12 Text_1
but if i copy formula into rows in B columns it dost work. thnx
Jan,
try this array formula:
Dear sir,
how do I use sum if to show for example to extract those quantity that match a date range like from 1.1.12 to 31.12.12
1.1.12 80 qty 80
1.11.11 50 0 (as this does fall within date range)
david,
Did you read my answer to srinivas?
https://www.get-digital-help.com/2009/12/13/formula-for-matching-a-date-within-a-date-range-in-excel/#comment-54360
Dear Sir,
I am not sure what I am doing wrong, the following is my data and formula I am using, I initially copy one of your examples and change the value field from A,B,C, ETC to 300,320,340, etc tried diff format (gen, num)added rows and keep getting #VALUE! error, made sure fields have same format as the example but once I added the additional rows I get the error, let me know if you can help.. your help is highly appreciated. Grace
=INDEX($B$3:$B$15,MIN(IF((DATEVALUE(RIGHT(C3:C15,LEN(C3:C15)-FIND("/",C3:C15)))>=C17)*(DATEVALUE(LEFT(C3:C15,FIND("/",C3:C15)-1))<=C17),MATCH(ROW($C$3:$C$15),ROW($C$3:$C$15)),"")))
B C
Value Date range
3 300 2013-07-01/2014-06-30
4 320 2012-07-01/2013-06-30
5 340 2011-07-01/2012-06-30
6 360 2010-07-01/2011-06-30
7 380 2009-07-01/2010-06-30
8 400 2008-07-01/2009-06-30
9 420 2007-07-01/2008-06-30
10 440 2006-07-01/2007-06-30
11 460 2005-07-01/2006-06-30
12 480 2004-07-01/2005-06-30
13 500 2003-07-01/2004-06-30
14 520 2002-07-01/2003-06-30
15 540 2001-07-01/2002-06-30
16
17 Date value: 7/1/2010
18 Search result: #VALUE!
Grace Langford,
You forgot to enter it as an array formula.
Download excel *.xlsx file
Grace-Langford.xlsx
Many thanks, Oscar. I'm tired and cross-eyed and your formula saved me a lot of time. Worked beautifully!
Hello Oscar!
My situation is similar, but not identical to your examples:
Sheet 1 provides a date range in two cells:
1.2013 2.2013
2.2013 3.2013...
Sheet 2 provides specific dates and values
3.1.2013 500$
5.2.2013 700$...
What I want to do now is insert the Values from Sheet 2 next to the right date ranges in Sheet 1. Please note that there is only one date (and thus one value) for each date ranch.
1.2013 2.2013 500$
2.2013 3.2013 700$
Usually I would use Vlookup and match the dates, but that doesn't work since I have a date range.
I would be very thankful for your help. This problem seems so easy but I can't seem to figure out a solution!
Thanks and best regards!
Hi Oscar,
Based on the staff's joining date, the excel file should display the Percentage entitlement for that staff
Description Grade %
Joined after 01.07.2013 to 31.12.2014 8 % Nil
Joined between 01.07.2012 and 30.06.2013 8 % 5
Joined between 01.07.2011 and 30.06.2012 8 % 8.33
Joined between 01.07.2010 and 30.06.2011 8 % 16.66
Joined between 01.07.2003 to 01.07.2010 8 % 20
joined between 01.07.2003 to 30.06.2013 7 % 25
Pls. could U assist?
Rgds
Hi Oscar
I am new to your site and to posting.
I am in need of some help.
I have 2 data sets. First data set contains 3 columns of data: 1)vehicle unit#, a driver ID# and the date/time of a transaction.
The problem is that often this dataset has a null value for the tractor#, so I need to find out what tractor this driver had during the time the transaction occurred.
I have a second file obtained from OBC (On-board Computer) data that contains Login times and logout times as well as the driver#, Tractor#,Login Date/Time, Logout Time.
There can be many drivers in file 2 that are logged in with a date range that could include transaction date from file 1, so I need to be able to find not only the date within a range, but specific to the driverID.
IE: File 1
DriverId Tractor# Transaction Date
MORT 02/02/2015 04:34:00
MORT 02/02/2015 18:04:00
MORT 02/02/2015 18:59:00
JOHA 02/02/2015 03:35:00
LITMAR 02/02/2015 03:46:00
RODB 02/02/2015 10:15:00
Sorry - Sent previous post without finishing:
IE: File 1
DriverId Tractor# Transaction Date
MORT 02/02/2015 04:34:00
MORT 02/02/2015 18:04:00
MORT 02/02/2015 18:59:00
JOHA 02/02/2015 03:35:00
LITMAR 02/02/2015 03:46:00
RODB 02/02/2015 10:15:00
IE: File 2
Driver ID Vehicle# Login Date/Time Logout Date/Time
MORT 040 02/02/2015 05:20:09 02/02/2015 15:11:25
JOHA 318 02/02/2015 01:35:00 02/02/2015 05:35:00
LITMAR 5129 02/02/2015 01:46:00 02/02/2015 05:46:00
RODB 5101 02/02/2015 01:15:00 02/02/2015 11:15:00
I have daily temperature data for over 50 stations ranging from 2005-2012. I am looking for a formula that will give me the station Name, date closest 12/31/2012, and the temperature value for that date.
I tried vlookup/match/INDEX lookup up or match station than I am unable to index date
My column A is DATE, column B has letters A-H which can repeat and varies with the dates, columns C and D have integers. I am looking for a formula to be in a cell in column E that can give me the value from either C/D if a cell value in column F (can be letters from A-H) matches with the letter in column B and the date is the MOST RECENT DATE. Thanks to everyone who can help me.
Hi,
can anybody help me solve following problem.
Table1
item no - start date - end date - value
A 01.01.16 21.02.16 10
A 22.02.16 31.12.16 20
B 01.01.16 31.12.16 30
Depending on a specific date within the range, the correct value shall be used in following formula:
item no date value
A 20.02.16 VLOOKUP(item no;Table1;4)- Result 10
B 25.02.16 VLOOKUP(item no;Table1;4)- Result 30
A 25.02.16 VLOOKUP(item no;Table1;4)- Result 20
Hi Oscar,
I have
name1 | division |...other columns...| start date of vacation | end date of vac (but start date and end date of vacation are in more than two columns (they are using more than one part of vacation ex: start | end, start|end...)
i need to extract name1, division, start date, end date for any given date
Thank You
counting people on vacation
Hi,
I have a set date that is a baseline. I can move it 60days either side as a maximum.
Qs. I have 300 rows of data each has its own populated baseline date (say row 1 is 30-6-2016 and row 300 is 25-9-2023) but want to align dates by moving some forward and some back for efficiency.
Do I have to first individually make a start date and end date range on each row from the baseline date or is there a formula that can say -60 days (30-6-2016) +60 days
Hi Oscar,
Could you help me solve this problem. I have sets of data both our date based. i.e, what I have below.
4.1.1990 33234 2.1.1990 2345
5.1.1990 33245 4.1.1990 2356
6.1.1990 33265 5.1.1990 2354
7.1.1990 33678 6.1.1990 2367
7.1.1990 2314
What I am trying to do is match the data from the second set of data to the first set of data so that the date lines up. The extra days in the second data will be disregarded as i don't need them.
I have tried many different types of formulas but till not can't find a solution.Doing this manually takes way to long as some of my data spans 25 years.
If you can help I would be very thankful :-)
Hi Merlin
I would separate the contents of one cell into multiple columns, on both data sets. Text-to-columns allows you to do that.
You find Text-to-columns on tab "Data", on the ribbon.
Use INDEX + MATCH to line the dates up.
Formula in cell C1:
=INDEX(Sheet2!A$1:A$5, MATCH(Sheet1!$A1, Sheet2!$A$1:$A$5, 0))
Copy cell C1 (not formula) and paste it to remaining cells in column C and D.
I have a date range A1(12/06/2016) to B1(20/10/2016) and date of joining(DOJ) in C1(15/07/2000). In D1 I want to return 20 otherwise "" if only the DOJ(D1) 15th July matches the date range regardless of the year.
Note: The date format is in (dd/mm/yyy).
I have searched the web but yet to find an answer of this kind. An answer in IF formula(excel) will be great since I don't understand SQL.
Thanks in advance.
John Sanil
John Sanl
This formula works for me:
Thanks Oscar,
Your formula gave me a good relief which I had been searching the web for a long time.
But Sorry, I think, I misinterpreted the way I put in the query.
D1 works perfectly till 31/12/2016 but has it crosses over to the next year say 01/03/2017 (dd-mm-yyy) the answer shows blank even though date of joining ie. 15th July is within date range 12/06/2016 to 01/03/2017. Is there a possible solution for this?
Thanks in advance for the answer
John
John Sanl
I understand, this formula seems to do the work:
=IF(YEAR(A1)=YEAR(B1), IF((DATE(1900,MONTH(A1), DAY(A1))<=DATE(1900,MONTH(C1), DAY(C1)))*(DATE(1900,MONTH(B1), DAY(B1))>=DATE(1900, MONTH(C1),DAY(C1))),20,""), IF((DATE(1900,MONTH(A1), DAY(A1))>DATE(1900, MONTH(C1),DAY(C1)))*(DATE(1900, MONTH(B1), DAY(B1))
Please help
=IFERROR(INDEX(B$4:B$16;SMALL(IF(($B$24>=$I$4:$I$16)*($B$24=$N$4:$N$16)*($B$24<=$P$4:$P$16);MATCH(ROW($B$4:$B$16);ROW($B$4:$B$16)));ROW(A1)));"")
how can I combine these two ranges into one (actualy i have 4 - I and J, N and P,....)? It needs to work like an OR function
Thank You
=IFERROR(INDEX(B$4:B$16;SMALL(IF(($B$24>=$I$4:$I$16)*($B$24=$N$4:$N$16)*($B$24<=$P$4:$P$16);MATCH(ROW($B$4:$B$16);ROW($B$4:$B$16)));ROW(A1)));"")
getting a value error using formula below:
=INDEX($B$2:$B$77,MIN(IF((DATEVALUE(RIGHT(C2:C77,LEN(C2:C77)-FIND("/",C2:C77)))>=C80)*(DATEVALUE(LEFT(C2:C77,FIND("/",C2:C77)-1))<=C80),MATCH(ROW($C$2:$C$77),ROW($C$2:$C$77)),"")))
tony,
Use "Evaluate Formula" on tab "Formulas" on the ribbon.
That will tell you where the error is.
Great Thanks to you as the solutions provided are terribly simple after days of searching for something similar!
Hi, thanks for the formula. In the case of "Match a date when a date range is entered in two cells"
What is the value if no range is matched? How can the formula return a default value in case it doesn't match any range?
Thanks in advance!
Ray