Get date ranges from a schedule

This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier Excel versions.

1. Get date ranges from a schedule - Excel 365

The following formulas extract names and date ranges from the schedule in sheet1, the schedule has dates in row 2 and names column A.

The "x" indicates populated days. The formulas use the "x" to extract the correct name and the corresponding dates. Note that there may be multiple date ranges in the schedule for the same person.

Excel 365 dynamic array formula in cell B3:

Excel 365 dynamic array formula in cell C3:

Excel 365 dynamic array formula in cell D3:

1.1 Explaining formula in cell B3

The formulas in cell C3 and D3 are similar to the formula in cell B3, they extract start and end dates instead of names.

Step 1 - Compare cell ranges and check if values are not equal

The less than and larger than signs are logical operators, they return TRUE or FALSE which are boolean values. The less than and the greater than signs combined means "not equal to"

Sheet1!B3:NA7<>Sheet1!C3:NB7

becomes

{0,0,0,"X", ... ,0}<>{0,0,"X","X", ... ,0}

These are big arrays, and I have shortened them.

{0,0,0,"X", ... ,0}<>{0,0,"X","X", ... ,0}

returns

{FALSE,FALSE,TRUE,FALSE, ... , FALSE}

Note that the cell references are not perfectly aligned, the last cell reference (red) is offset one cell to the right. This technique lets us pinpoint when a populated cell range begins and ends.

The way comparing cell ranges works is that a cell in one cell range is compared to the corresponding cell in the second cell range. For example, cell B2 is compared to C2 and only C2. Cell B3 is compared to cell C3 and so on.

Step 2 - Check if cells are empty

The equal sign is also a logical operator, the following part cheks if a cell is empty "".

Sheet1!B3:NA7=""

becomes

{"","","","X", ... ,""}=""

and returns

{TRUE,TRUE,TRUE, FALSE, ... ,TRUE}

Step 3 - Multiply arrays

The asterisk lets you multiply numbers and boolean values in an Excel formula. This lets you create AND logic between boolean values meaning both values must be TRUE to return TRUE.

TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * TRUE = FALSE (0)
FALSE * FALSE = FALSE (0)

Excel converts the result to the boolean values numerical equivalents.

TRUE = 1
FALSE = 0

(Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7="")

becomes

{FALSE,FALSE,TRUE,FALSE, ... , FALSE}*{TRUE,TRUE,TRUE, FALSE, ... ,TRUE}

and returns

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

Step 4 - Replace with names from column A

Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,"")

becomes

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

and returns

{"","","Graham Chapman", ... ,""}

Step 5 - Rearrange values to a single column layout

Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL(IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,""))

becomes

TOCOL({"","","Graham Chapman", ... ,""})

and returns

{"";"";"Graham Chapman"; ... ;""}

Note that the semicolons changed to commas. They are delimiting characters in an array, you may have other characters as they are based on the regional settings of your computer.

Step 6 - Check if cells are not empty

This step is to create a logical test that is then used in the next step to filter out empty values.

TOCOL(IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,""))<>""

becomes

{"";"";"Graham Chapman"; ... ;""}<>""

and returns

{FALSE;FALSE;TRUE; ... ;FALSE}

Step 7 - Filter out empty values

Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(TOCOL(IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,"")),TOCOL(IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,""))<>""))

becomes

FILTER({"";"";"Graham Chapman"; ... ;""},Sheet1!A3:A7,"")),{FALSE;FALSE;TRUE; ... ;FALSE})

and returns

{"Graham Chapman"; "Graham Chapman"; "Graham Chapman";  ... ; "Michael Palin"}

Step 8 - Shorten formula

Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

FILTER(TOCOL(IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,"")),TOCOL(IF((Sheet1!B3:NA7<>Sheet1!C3:NB7)*(Sheet1!B3:NA7=""),Sheet1!A3:A7,""))<>""))

x - Sheet1!B3:NA7
y - Sheet1!C3:NB7
w - TOCOL(IF((x<>y)*(x=""),Sheet1!A3:A7,""))

LET(x,Sheet1!B3:NA7,y,Sheet1!C3:NB7,w,TOCOL(IF((x<>y)*(x=""),Sheet1!A3:A7,"")),FILTER(w,w<>""))

Back to top

1.2 Get Excel *.xls file

Back to top

2. Get date ranges from a schedule - earlier Excel versions

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:

1. Sheet1!$A$3:$A$7 is the cell reference to the names in the schedule.
2. 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.

How to enter an array formula

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.

Back to top

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)

becomes

{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)

becomes

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

INDEX(Sheet1!$A$3:$A$7, 1)

becomes

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

and returns "Graham Chapman" in cell B3.

Back to top

Back to top

3. Extract dates from a cell block schedule

Answer:

The image above shows three different formulas that extract groups based on adjacent text names from a calendar, the calendar is shown in the top image. The only exception is a range that spans over at least two months, it will be divided into two date ranges.

Excel 365 dynamic array formula in cell B3:

The following formulas are for older Excel versions:

Array Formula in cell B3:

Array Formula in cell C3:

Formula in cell D3:

To enter an array formula, type the formula in a cell 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.

Copy cell A2 and  paste it down as far as needed.

Explaining formula in cell B3

Step 1 - Compare cell ranges

The less than and greater than signs combined returns TRUE if a cell is not equal to the next.

Sheet1!$C$3:$AG$14<>Sheet1!$B$3:$AF$14

becomes

{"G", "G", 0, ... I made the array shorter ... , 0}<>{0, "G", "G", ... , 0}

and returns

{TRUE, FALSE, TRUE, ...  , FALSE}

Step 2 - Check if cell is not empty

Sheet1!$C$3:$AG$14<>""

returns

{TRUE, TRUE, FALSE, ... , FALSE}

Step 3 - Multiply arrays

(Sheet1!$C$3:$AG$14<>Sheet1!$B$3:$AF$14)* (Sheet1!$C$3:$AG$14<>"")

becomes

{TRUE, FALSE, TRUE,  ... , FALSE}* {TRUE, TRUE, FALSE,  ... , FALSE}

and returns

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

Step 4 - Convert array into dates

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3). The following lines explain the logical expression:

IF((Sheet1!$C$3:$AG$14<>Sheet1!$B$3:$AF$14)* (Sheet1!$C$3:$AG$14<>""), DATE(2018, ROW($1:$12), Sheet1!$C$2:$AG$2), "")

becomes

IF({1,0,0,... ,0}, DATE(2018, ROW($1:$12), Sheet1!$C$2:$AG$2), "")

becomes

IF({1,0,0,... ,0}, DATE(2018, {1;2;3;4;5;6;7;8;9;10;11;12}, Sheet1!$C$2:$AG$2), "")

and returns

{43101,"","", ... ,""}

Step 5 - Extract dates

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF((Sheet1!$C$3:$AG$14<>Sheet1!$B$3:$AF$14)* (Sheet1!$C$3:$AG$14<>""), DATE(2018, ROW($1:$12), Sheet1!$C$2:$AG$2), ""), ROWS($A$1:A1))

becomes

SMALL({43101,"","", ... ,""}, ROWS($A$1:A1))

becomes

SMALL({43101,"","", ... ,""}, 1)

and returns 43101 (1/1/2018) in cell B3.

Array Formula in cell C3:

Copy cell B2 and  paste it down as far as needed. I am not going to explain this formula, it is very similar to the one in cell B3.

Formula in cell D3:

Copy cell C2 and  paste it down as far as needed.

Explaining formula in cell D3

Step 1 - Calculate month number from 1 to 12 based on date

The MONTH function extracts the month as a number from an Excel date.

MONTH(B3)

becomes

MONTH(1/1/2018)

becomes

MONTH(43101)

and returns 1.

Step 2 - Calculate day number based on date

The DAY function extracts the day as a number from an Excel date.

DAY(B3)

becomes

DAY(1/1/2018)

becomes

DAY(43101)

and returns 1.

Step 3 - Get text name based on month and day

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX(Sheet1!$C$3:$AG$14,MONTH(B3),DAY(B3))

becomes

INDEX(Sheet1!$C$3:$AG$14,1,1)

and returns "G" in cell D3.

Dates category

Schedule category

Two dimensional lookup category

More than 1300 Excel formulas

Excel categories

Comments (1)