## Get date ranges from a schedule

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based on a cross-reference schedule.

The schedule has dates in row 2, however, the cell is formatted to show only the day number.

Names are in column A and there are no duplicate names. An X indicates that a date is selected for the given person, multiple x's in a sequence is a date range.

The array formula in cell B3 returns the correct number names needed to return all date ranges:

Array formula in cell C3:

Array formula in cell D3:

- Sheet1!$A$3:$A$7 is the cell reference to the names in the schedule.
- Sheet1!$B$3:$AA$7 and Sheet1!$C$3:$AB$7 are a cell reference to the X's in the schedule.

Two are needed for the formula to count the number of date ranges.

Note! The cell refs have the same size, however, the latter is offset by one column.

To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

### Explaining the formula in cell B3

Use "Evaluate Formula" on tab "Formula" on the ribbon to go through the steps.

#### Step 1 - Count previous values

COUNTIF($B$2:B2,Sheet1!$A$3:$A$7)

becomes

COUNTIF("Name", {"Graham Chapman"; "John Cleese"; "Eric Idle"; "Terry Gilliam"; "Michael Palin"})

and returns {0;0;0;0;0}. None of the names have been displayed in cells above, remember that I am right now showing the calculation steps in cell B3.

As soon as the first name has been displayed the same number of times as it has date ranges the formula continues to the second name.

We are now going to count how many date ranges there is in the schedule per row.

#### Step 2 - Calculate where a cell value changes in a row

To count date ranges the formula needs to compare a cell with the next. To be able to compare all values in one calculation I use two cell ranges. The last cell range has the same size as the first, however, it is offset one column to the right.

(Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1

becomes

{0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0;0, 1, 1, 0, 0, 0, ... , 0}

Part of the array is displayed in cell range B8:X12 in picture below.

#### Step 3 - Sum values per row

The amazing MMULT function allows you to sum numbers in an array per row or column. The earlier calculation step created an array that shows when a cell has a different value compared to the next cell.

That makes the array show when a date range starts and ends, to be able to count date ranges we must divide the sum of each row with 2.

(MMULT((Sheet1!$B$3:$AA$7 <> Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2)

becomes

({6; 6; 2; 6; 4}/2)

and returns {3; 3; 1; 3; 2}.

The array tells us that the first name has three date ranges, the second name has three date ranges and so on.

The image above shows that the first name has three date ranges, the second has 3, the third has 1, the fourth has 3, and the last name has 2 date ranges.

The calculation is correct.

#### Step 4 - Compare arrays

The number of names displayed must match the corresponding number of date ranges.

To do that I compare the arrays, if they are equal the logical expression returns TRUE. If not equal it returns FALSE.

COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2)

{0;0;0;0;0}={3; 3; 1; 3; 2}

and returns {FALSE; FALSE; FALSE; FALSE; FALSE}.

#### Step 5 - Find position of first name that is shown less times than there is corresponding date ranges

The MATCH function allows you to identify the position of a specific value in an array or cell range, if the third argument in the MATCH function is 0 (zero) meaning EXACT match.

MATCH(FALSE, COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2), 0)

MATCH(FALSE, {FALSE; FALSE; FALSE; FALSE; FALSE}, 0)

and returns 1.

#### Step 6 - Get the name

The INDEX function gets the first value in cell range Sheet1!$A$3:$A$7.

INDEX(Sheet1!$A$3:$A$7, MATCH(FALSE, COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2), 0))

becomes

becomes

INDEX({"Graham Chapman"; "John Cleese"; "Eric Idle"; "Terry Gilliam"; "Michael Palin"}, 1)

and returns "Graham Chapman" in cell B3.

### Download Excel *.xlsx file

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 [โฆ]

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 [โฆ]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to [โฆ]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each [โฆ]

Extract records containing digits [Formula]

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the [โฆ]

Populate cells dynamically in a weekly schedule

In this post I am going to add one more function to the weekly schedule I built in a previous [โฆ]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of [โฆ]

Highlight specific time ranges in a weekly schedule

In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to [โฆ]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of [โฆ]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) [โฆ]

Gets a value in a specific cell range based on a row and column number.

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