Multi-cell array formula :: Calendar


1. About the calendar formula


Description: returns the calendar for the current month. Row start Monday

Syntax: IF(logical_test,value_if_true,value_if_false)

The array formula (in R1C1 style): =IF(MONTH(DATE(YEAR(MthYr),MONTH(MthYr),1)) <> MONTH(DATE(YEAR(MthYr),MONTH(MthYr),1) - (WEEKDAY(DATE(YEAR(MthYr),MONTH(MthYr),1))-1) + (ROW(R1:R6)-1) * 7 + TRANSPOSE(ROW(R1:R7)-1)), "", DATE(YEAR(MthYr), MONTH(MthYr),1) - (WEEKDAY(DATE(YEAR(MthYr), MONTH(MthYr),1))-1) + (ROW(R1:R6)-1) * 7 + TRANSPOSE(ROW(R1:R7)-1))


Fig 1: Excel Web App #1 - showing the calendar for the current month

Note: this version of an array based calendar was outlined in a 90045 session in 1HY18. Other implementations of calendar formulas can be found as template files in the Excel, New File, backstage view: Academic calendar, Any year one-month calendar, ...


2. Calendar - setting up the formula in Excel


A screen shot of the calendar for March 2018 is shown in figure 2. This is needed in the following discussion because the image in figure 1 is dynamic. Excel OneDrive based files do not permit Form Controls, ToolBox, or ActiveX items. These features are available in the download version at the foot of this page.


calendar march 2018
Fig 2: March 2018 - static image - the array formula spans the range R14C2:R19C8

To setup the formulas manually, follow logic steps 1 to 11. See section 3 for VBA code to perform the setup (available in a later version).

  1. Defined Name: MthYr
  2. Formula: =TODAY equal to March, 2018 in the static version

The array formula (see part 1):

  1. IF function logical_test
    Component: MONTH(MthYr) <> MONTH(MthYr) - (WEEKDAY(DATE(YEAR(MthYr),MONTH(MthYr),1),0)-1) + (ROW(R1:R6)-1) * 7 + TRANSPOSE(ROW(R1:R7)-1))
    Comparison operator <> (not equal to)
  2. Element - left of comparison: MONTH(MthYr), return the number of the month → 3
  3. Component: DATE(YEAR(MthYr), MONTH(MthYr),1) - (WEEKDAY(DATE(YEAR(MthYr), MONTH(MthYr),1),0) - 1)
    returns DATE(2018, 3, 1) - (WEEKDAY(DATE(2018, 3, 1)) - 1)
    returns 43160 - (5 - 1). That is serial date 43156, the 25th Feb 2018
  4. ROW(R1:R6)-1) * 7 returns a column vector (6 x 1) with elements {0;7;14;21;28;35}
    In matrix format: $$ \left[ \begin{matrix} 1 \\ 2 \\ 3 \\ 4 \\ 5 \\ 6 \\ \end{matrix} \right] - 1 = \left[ \begin{matrix} 0 \\ 1 \\ 2 \\ 3 \\ 4 \\ 5 \\ \end{matrix} \right], \quad \quad \left[ \begin{matrix} 0 \\ 1 \\ 2 \\ 3 \\ 4 \\ 5 \\ \end{matrix} \right] * 7 = \left[ \begin{matrix} 0 \\ 7 \\ 14 \\ 21 \\ 28 \\ 35 \\ \end{matrix} \right] $$
  5. TRANSPOSE(ROW(R1:R7) - 1) returns a row vector (1 x 7) with elements {0,1,2,3,4,5,6}
    In matrix format: $$ \left[ \begin{matrix} 1 \\ 2 \\ 3 \\ 4 \\ 5 \\ 6 \\ 7 \\ \end{matrix} \right] ^\intercal - 1 = \left[ \begin{matrix} 1 & 2 & 3 & 4 & 5 & 6 & 7 \\ \end{matrix} \right] - 1 = \left[ \begin{matrix} 0 & 1 & 2 & 3 & 4 & 5 & 6 \\ \end{matrix} \right] $$
  6. (ROW(R1:R6) -1 ) * 7 + TRANSPOSE(ROW(R1:R7) - 1) returns an array (6 x 7) with elements {0,1,2,3,4,5,6;7,8,9,10,11,12,13;14,15,16,17,18,19,20;21,22,23,24,25,26,27;28,29,30,31,32,33,34;35,36,37,38,39,40,41}
    In matrix format: $$ \left[ \begin{matrix} 0 \\ 7 \\ 14 \\ 21 \\ 28 \\ 35 \\ \end{matrix} \right] + \left[ \begin{matrix} 0 & 1 & 2 & 3 & 4 & 5 & 6 \\ \end{matrix} \right] = \left[ \begin{matrix} 0 & 1 & 2 & 3 & 4 & 5 & 6 \\ 7 & 8 & 9 & 10 & 11 & 12 & 13 \\ 14 & 15 & 16 & 17 & 18 & 19 & 20 \\ 21 & 22 & 23 & 24 & 25 & 26 & 27 \\ 28 & 29 & 30 & 31 & 32 & 33 & 34 \\ 35 & 36 & 37 & 38 & 39 & 40 & 41 \\ \end{matrix} \right] = A $$

  7. Adding the scalar constant 43156 (see point 5) to each of the elements in the array \(A\) of point 8, returns:
    $$ A + 43156 = \left[ \begin{matrix} 43156 & 43157 & 43158 & 43159 & 43160 & 43161 & 43162 \\ 43163 & 43164 & 43165 & 43166 & 43167 & 43168 & 43169 \\ 43170 & 43171 & 43172 & 43173 & 43174 & 43175 & 43176 \\ 43177 & 43178 & 43179 & 43180 & 43181 & 43182 & 43183 \\ 43184 & 43185 & 43186 & 43187 & 43188 & 43189 & 43190 \\ 43191 & 43192 & 43193 & 43194 & 43195 & 43196 & 43197 \\ \end{matrix} \right] $$
  8. Then filter with the logical test to display only dates for month 3:
    $$ \left[ \begin{matrix} & & & & 43160 & 43161 & 43162 \\ 43163 & 43164 & 43165 & 43166 & 43167 & 43168 & 43169 \\ 43170 & 43171 & 43172 & 43173 & 43174 & 43175 & 43176 \\ 43177 & 43178 & 43179 & 43180 & 43181 & 43182 & 43183 \\ 43184 & 43185 & 43186 & 43187 & 43188 & 43189 & 43190 \\ & & & & & & \\ \end{matrix} \right] $$
  9. Apply a date format. Category: Custom, Type: d


The ROW function returns an array


Excel functions Description
ROW(reference)
Returns the row number of a reference, or the first row number of a multi-row reference. When entered as an array formula with a multi row reference, ROW returns a vector of digits from the first row to the last.
Example: ROW(R3:R7) returns {3,4,5,6,7} as a CSE formula in R1C1 style