MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Calendar in Excel with One Formula (Array Entered, of Course!)


February 05, 2018 - by Bob Umlas

Calendar in Excel with One Formula (Array Entered, of Course!)

Create calendar in Excel with one formula by using array-entered formula.

Look at this figure:

Calendar in Excel - December
Calendar in Excel - December

That formula, =Cool, is the same formula in every cell from B5:H10! Look:

Base calendar formula
Base calendar formula

It was array-entered once B5:H10 was first selected. In this article you will see what is behind the formula.

By the way, there's a cell which isn't shown yet which is the month to display. That is, cell J1 contains =TODAY(), (and I'm writing this in December) but if you change it to 5/8/2012, you would see:


Month changed to May
Month changed to May

This is May, 2012. OK, definitely cool! Start from the beginning, and work your way up to this formula in the calendar and see how it works.

Also, assume that today is May 8, 2012.

First, look at this figure:

Sample formula
Sample formula

The formula doesn't really make sense. It would, if it were surrounded by =SUM, but you want to see what's behind the formula, so you will expand it by selecting it and pressing the F9 key.

Select formula
Select formula

The figure above becomes the figure below when the F9 key is pressed.

What's behind the formula
What's behind the formula

Notice that there's a semi-colon after the 3 - this indicates a new row. New columns are represented by a comma. So you are going to take advantage of that.

The number of weeks in a month varies, but no calendar needs more than six rows to represent any month, and of course, they all have seven days. Look at this figure:

Calendar range
Calendar range

Manually enter the values 1 to 42 in B5:H10, and if you enter =B5:H10 in a cell and then expand the formula bar, you see what's shown here:

Expanded formula
Expand the formula in the formula bar

Notice the placement of the semicolons - after each multiple of 7 - indicating a new row. This is the start of the formula, but instead of such a long one, you can use this shorter formula. Select B5:H10. Type

={0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}

as the formula, but do not press Enter.

To tell Excel this is an array formula, you have to hold down Ctrl + Shift with your left hand. While holding Ctrl + Shift, press Enter with your right hand. Then, release Ctrl + Shift. For the rest of this article, this set of keystrokes will be called Ctrl + Shift + Enter.

If you did Ctrl + Shift + Enter correctly, curly braces will appear around the formula in the formula bar and the numbers 1 to 42 would appear in B5:H10 as shown here:

Curly braces around the formula
Curly braces around the formula

Notice that you are taking the numbers 0 through 5 separated by semicolons (new row for each) and multiplying them by 7, effectively giving this:

Expand more
Expand more - row index multipled by 7

The vertical orientation of these values added to the horizontal orientation of the values 1 through 7 does yield the same values as shown. The expansion of this is identical to what you had before. Suppose now you add TODAY to these numbers?

Note: Editing an existing array formula is very tricky. Carefully, follow these steps: Select B5:H10. Click in the Formula Bar to edit the existing formula. Type +J1 but do not press Enter. To accept the edited formula, press Ctrl + Shift + Enter.

The result for May 8, 2012 is:

The result for May 8, 2012
The result for May 8, 2012

These numbers are serial numbers (the number of days since 1/1/1900). If you format these as short dates:

Formatted range
Formatted range

Clearly not right, but you will get there. What if you format these as simply "d" for the day of the month:

Format as 'day' of the month
Format as 'day' of the month

Almost looking like a month, but no month starts with the ninth of the month. Ah, here's one problem. You used J1 which contains 5/8/2012, and you really need to use the date of the first of the month. So suppose you put =DATE(YEAR(J1),MONTH(J1),1) in J2:

Date of the first of the month
Date of the first of the month

Cell J1 contains 5/8/2012 and cell J2 changes that to the first of the month of whatever is entered in J1. So if you change J1 in the formula of the calendar to J2:

Change the base date as the first date of the month
Change the base date as the first date of the month

Closer, but still not right. One further adjustment is needed, and that is you need to subtract the weekday of the first day. That is, cell J3 contains =WEEKDAY(J2). 3 represents Tuesday. So now if you subtract J3 from this formula, you get:

Shift by the weekday
Shift by the weekday

And that's actually right for May, 2012!

Okay, You are real close. What's still wrong is the 29 and 30 from April is showing up in the May calendar, and June 1 thru 9 is also showing up. You need to clear these.

You can give the formula a name for easier reference. Call it "Cal" (not "cool" yet). See this figure:

Create a named formula
Create a named formula

Then you can change the formula to simply be =Cal (still Ctrl + Shift + Enter):

Change the array formula with the named formula
Change the array formula with the named formula

Now you can change the formula to read that if the result is in row 5 and the result is over 20, say, then that result should be blank. Row 5 will contain the first week of any month so you should never see any values over 20 (or any number over seven would be wrong - a number like 29 which you see in cell B5 of the figure above is from the previous month). So you can use =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):

Previous month's dates
Previous month's dates

First, notice that cells B5:D5 are blank. The formula now reads "if this is row 5, then if the DAY of the result is over 20, show blank".

You can continue to remove the low numbers at the end - next month's values. Here is how to do this easily.

Edit the formula and select the final reference to "Cal"

Next month's dates - 1
Next month's dates - 1

Begin typing IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal) to replace the final Cal.

Next month's dates - 2
Next month's dates - 2

The final formula should be

=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))

Press Ctrl + Shift + Enter. The result should be:

Result-1
Result-1

Two things left to do. You can take this formula and give it a name, "Cool":

Name the formula as 'Cool'
Name the formula as 'Cool'

Then use that in the formula shown here:

Result-2
Result-2

By the way, defined names are treated as if they are array-entered.

What's left to do is format the cells and put in the Days of the week and the name of the month. So you widen the columns, increase the row height, increase the font size, and align the text:

Format the range
Format the range

Then put borders around the cells:

Calendar borders
Calendar borders

Merge and center the month & year and format it:

Month name and year
Month name and year

Then turn off gridlines, and voila:

Final Result - Calendar
Final Result - Calendar

Title Photo: Amber Avalona / Pixabay


Excel Outside the Box

This guest article is from Excel MVP Bob Umlas. It is from the book, Excel Outside the Box. To see the other topics in the book, click here.