HI Brian,
Here's a simple calendar that uses three formulas. With no 'seed' number entered in row 2, it looks like this:
The first error triangle is due to the first Sat cell containing a null string (a text value). when a number (usually 1) is entered in any cell in row 2, the errors disappear, and the table looks like this calendar (for the current month, June, 2018):
You can show the first week of July dates by entering a 1 in the first cell after the last date in June, or you can blank the 'dates' after the 30th by selecting the cells containing 31 to 37, then pressing delete (as I've done below).
The easiest way to make calendars for additional months is to Duplicate the original table before entering the seed number. You could also Save the document (containing one copy of the original, unused table or 12 copies of the unused table) as a Template, giving you immediate access to a fresh set of tables for the next year.
The tables are reusable, and easy to edit for that re-use, but you do need to replace the missing formulas to do so.
Entering the seed number replaces the formula in that cell. Pressing delete after selecting the dates beyond the end of the month removes the formulas from those cells, so if you want to re-use the same table for a new month, you first need to replace the missing formulas.
There are three formulas in the table.
Cell A2 is empty.
B2, and filled right to G2: IF(LEN(A2)<1,"",A2+1)
IF checks the length of the entry in the cell to its left. If the cell is empty, or contains a null string (a text value with zero length), the comparison returns true, and IF inserts a null string in its cell. If the cell contains one or more characters, IF adds 1 and inserts the result in its cell. (The addition operator ( + ) will not accept a text value, and will return an error message if asked to add to one, hence the need for the IF statement in this row.)
A3: G2+1
IF is not necessary here, as G2 will always contain a number if the seed has been planted in any cell of row 2.
B3, and filled right to G3: A3+1
Again, IF is not necessary, as A3 will always contain an number after the seed value has been planted in row 2.
After filling B3 right, select all cells in row 3, then fill down to row 7.
Reusing a table:
Select the last row in which all cells contain a number, Fill down to row 7.
In row 2:
- If cell A2 contains 1, click once on that cell to select it, then press delete.
- If the 1 is in B2, select the cell to its right (C2) and fill left to B2.
- If the 1 is in any other cell in row 2, select the cell to the left of the 1, and fill right into the cell containing 1.
In the first case, you are creating an empt cell in A2. In the others, you are copying the 'first row' formula into the cell where it was replaced with the number 1.
Regards,
Barry
