Author: Oscar Cronquist Article last updated on August 05, 2017

In this post we are going to extract all "not empty" dates in a simple calendar. In a previous blog post we extracted only date ranges and names.

Here is a picture of the calendar.

Here is a picture of all extracted dates and corresponding "names".

### Array formula in cell A2:

=SMALL(IF(Sheet1!\$C\$5:\$AG\$16<>"", DATE(Sheet1!\$F\$2, ROW(Sheet1!\$C\$5:\$AG\$16) -MIN(ROW(Sheet1!\$C\$5:\$AG\$16))+1, Sheet1!\$C\$4:\$AG\$4), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

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

### Array formula in cell B2:

=INDEX(Sheet1!\$C\$5:\$AG\$16, SMALL(IF(Sheet1!\$C\$5:\$AG\$16<>"", ROW(Sheet1!\$C\$5:\$AG\$16) - MIN(ROW(Sheet1!\$C\$5:\$AG\$16))+1, ""), ROW(A1)), DAY(SMALL(IF(Sheet1!\$C\$5:\$AG\$16<>"", DATE(Sheet1!\$F\$2, ROW(Sheet1!\$C\$5:\$AG\$16) - MIN(ROW(Sheet1!\$C\$5:\$AG\$16))+1, Sheet1!\$C\$4:\$AG\$4), ""), ROW(A1)))) + CTRL + SHIFT + ENTER.

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

Extract-all-dates-and-name-from-yearly-block-schedule.xls
(Excel 97-2003 Workbook *.xls)

### Previous articles about cell blocks:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(
array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

Array formula in cell A2: