The obvious question from yesterdays blog post is: How to customize formula to extract dates for all months in calendar?

(I have added more data to the calendar since yesterday)

Answer:

Array formula in A2:

=SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(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 B2:

=SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$D$5:$AH$16)*(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.

Array formula in C2:

=INDEX(Sheet1!$C$5:$AG$16, SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(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<>Sheet1!$B$5:$AF$16)*(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 C2 and  paste it down as far as needed.

Download excel sample file for this tutorial.

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

Previous articles about cell blocks:

Functions in this article:

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