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.

Elizabeth asks:

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

Recommended articles

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

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.

LOOKUP(B3,E3:E9)

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.

Get the Excel file

Find-date-range.xlsx

Dates category

Create a date range [Formula]
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 the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

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

Excel categories

Comments (5)