## Extract dates from a cell block schedule

**Sam asks:**

One more question for the Calendar that you have set up above can we have a excel formula which will give us a below table

StarWk EndWk Name

1 2 G

4 6 G

7 15 R ... and so on

Question found here.

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.

**Array Formula in cell B3:**

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.

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

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

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]

The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]

This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar […]

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]

What's on this page How to use this Excel Calendar How to add events How I built this calendar Worksheet […]

I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]

This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]

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

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

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

### One Response to “Extract dates from a cell block schedule”

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

Oscar,This is great work...thanks for your time