## 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

Calculate date given weekday and week number

This article demonstrates a formula that returns a date based on a week number and a weekday like Sun to […]

Calculate date given weekday and week number

**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

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

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)) + […]

How to calculate overlapping time ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

### 50 Responses to “Formula for matching a date within a date range”

### Leave a Reply

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

**Contact Oscar**

You can contact me through this contact form

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

How to retrieve information from another sheet?

Match name by latest 4 max dates.

Sheet 1 has information

ie ; Column A Name & appears 20 times

; Column B Dates & same date appears 100 times

; Column C Distance

; Last Column is Z

Sheet 3 formula match name with latest 4 dates & across columns to last column Z

; Column A6 references Name ; Formula Column N6 Max Date ; M6

; Formula Column N7 2nd max Date ; O7

; Formula Column N8 3rd max Date ; O8

; Formula Column N9 4th max Date ; O9

Formula eg; =VLOOKUP(A6,'QLD RESULTS'!A:A,1,FALSE)

=VLOOKUP(A6,'QLD RESULTS'!A:B,2,FALSE)

=VLOOKUP(A6,'QLD RESULTS'!A:C,3,FALSE)

Regards

Tony

=INDEX($B$3:$B$6, SMALL(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))), ROW(A1)))

What is the meaning of ROW(A1)?

Can you also give step by step break down of this formula? I am having hard time understanding this.