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

Cell B3 contains the condition and column E and F contain the date ranges.

I need to figure out the date range of a cell. So if cell "E2" has a date of 11/23/2012 then that would need to fall under date range of "11/19 - 11/25".

I have the following date range I need to identify:

"11/12 - 11/18", "11/19 - 11/25", "11/26 - 12/2", "12/3 - 12/9", "12/10 - 12/16", "12/17 - 12/23", "12/24 - 1/1/2013".

I tried the formula below but I keep getting a result of "False".

I appreciate any help I can get. Thank you

**Answer:**

The formula in cell C3 returns the correct date range from the lookup table based on the date in cell B3.

Array formula in cell C3:

The lookup table consists of dates beginning with Monday and ends with Sundays.

For example, 11/12/2012 is a Monday and 11/18/2012 is a Sunday.

There are no gaps between the end and start date so the LOOKUP function only needs the first column for it to work.

The following formula calculates the dates if you don't want to use a lookup table.

This formula works only if the date range starts with a Monday and ends with a Sunday.

Recommended article

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Formula for matching a date within a date range

### Explaining array formula in cell C3

#### Step 1 - Find the matching start date

The LOOKUP function allows you to search for a value and return the largest value that is smaller than the lookup value.

For this to work the table must be sorted from small to large, this is important. You will get unreliable results if this requirement is not met.

Excel dates are actually numbers, 1/1/1900 is 1 and 1/1/2018 is 43101.

Since dates are numbers in Excel you can easily use the LOOKUP function in this case.

becomes

LOOKUP(41236, {41225; 41232; 41239; 41246; 41253; 41260; 41267})

and returns 41232.

41232 is the largest number that is smaller than 41236 in the array.

#### Step 2 - Convert serial number to Excel date

The TEXT function allows you to convert the serial number to a date. The second argument in the TEXT function lets you specify how the date shall look like.

TEXT(LOOKUP(B3,E3:E9),"MM/DD/YYYY")

becomes

TEXT(41232,"MM/DD/YYYY")

and returns 11/19/2012.

#### Step 3 - Find the matching end date

The LOOKUP function lets you also to return a corresponding value on the same row if you enter the third argument in the LOOKUP function.

LOOKUP(B3,E3:E9,F3:F9)

becomes

LOOKUP(41236,{41225; 41232; 41239; 41246; 41253; 41260; 41267},{41231; 41238; 41245; 41252; 41259; 41266; 41275})

and returns 41238 which is 11/25/2012.

The TEXT function converts the serial number to an Excel date, see step 2 again if you need to see the details again.

#### Step 4 - Concatenate the two Excel dates

The & ampersand allows you to concatenate two values in one cell.

TEXT(B3-WEEKDAY(B3,2)+1,"MM/DD/YYYY")&"-"&TEXT(B3-WEEKDAY(B3,2)+7,"MM/DD/YYYY")

becomes

11/19/2012&"-"&11/25/2012

and returns 11/19/2012-11/25/2012 in cell C3.

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

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

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

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

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

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

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

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]

### 5 Responses to “Find date range based on a date”

### 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 formula should work for any date...

=TEXT(A1-WEEKDAY(A1,3),"mm/dd-")&TEXT(A1-WEEKDAY(A1,3)+6,"mm/dd")

Rick Rothstein (MVP - Excel),

Thanks!

Hi Oscar, found your website. I am complete Excel novice, and have been asked to setup a 'Calendar type' list which identifies alternate wks of the year as 'wk1 & wk2' for work planning purposes. The idea I think will be that this 'calendar list' will be a link/or dropdown to a planning tool, for staff to check whether they are in a wk1 or wk2! No idea where to start really, can you make any suggestions, simple pse as only used to filling in spreadsheet for work not actually setting them up!

Any suggestions gratefully received.

Karen.

@Karen,

Some questions...

1) What day of the week does your wk1 or wk2 start on (Mon, Tue, etc.)?

2) Show us an actual date that would be identified as being in wk1 (need that to orient your work calendar to the real calendar).

3) Are you seeking a VBA or Excel Formula solution?

3)

I need help to convert the list of dates into date range based on a criteria for a grouping by the employee id for example:

Data is as below:

EMPLOYEE ID Dates

24900002 2/27/2017

24900002 3/20/2017

24900005 3/3/2017

24900011 3/13/2017

24900014 3/9/2017

24900022 3/13/2017

24900023 2/25/2017

24900024 2/21/2017

24900024 2/22/2017

24900024 2/23/2017

24900024 2/24/2017

24900024 2/27/2017

24900024 2/28/2017

outcome required is as below:

Start Date End Date

24900002 2/27/2017 2/27/2017

24900002 3/20/2017 3/20/2017

24900005 3/3/2017 3/3/2017

24900011 3/13/2017 3/13/2017

24900014 3/9/2017 3/9/2017

24900022 3/13/2017 3/13/2017

24900023 2/25/2017 2/25/2017

24900024 2/21/2017 2/24/2017

24900024 2/27/2017 2/28/2017