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))
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.

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).
- Defined Name: MthYr
- Formula:
=TODAY
equal to March, 2018 in the static version
The array formula (see part 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) - Element - left of comparison:
MONTH(MthYr)
, return the number of the month → 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 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] $$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] $$(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 $$- 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] $$ - 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] $$ - 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 |
- Download the Excel file for this module: xlf-calendar-array-formula.xlsx [11 KB]. Set to A1 reference style
- Development platform: Excel 2016 (64 bit) Office 365 ProPlus and VBA 7.1
- Draft version: 7th March 2018
- Revised: Friday 24th of February 2023 - 02:38 PM, Pacific Time (PT)