Formula for matching a date within a date range in excel
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
- Match a date when date ranges sometimes overlap and return multiple results
Match a date when a date range is entered in a single cell
Array formula in C9:
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
Download excel file, see sheet Ex 1
matching-a-date-in-a-date-range v2.xls
(Excel 97-2003 Workbook *.xls)
Match a date when a date range is entered in two cells
Array formula in cell C9:
How to enter an array formula
- Select cell C9
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter
Download excel file, see sheet Ex 2
matching-a-date-in-a-date-range v2.xls
(Excel 97-2003 Workbook *.xls)
Match a date when date ranges sometimes overlap and return multiple results
Array formula in cell C9:
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 v2.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
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
FIND(find_text;within_text;[start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive
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 posts:
Count matching cells in date range in excel
Finding the nearest date in a range of dates using excel formula
Sort dates within a date range using excel array formula
Recommended categories
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: http://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: