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.
Get Excel *.xlsx file
Dates category
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 to create unique distinct year and months from a long date listing (column A)? You can find the […]
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 […]
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 […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]
Schedule category
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 […]
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]
The image above demonstrates conditional formatting highlighting hours outside work hours, those cells are filled with grey except weekends. Conditional formatting […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
Two dimensional lookup category
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
The following formula performs a two-way lookup in two different tables.
Functions in this article
More than 600 Excel formulas
Excel categories
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.