Calendar spreadsheet: explained
I tidied up the calendar spreadsheet last night while awaiting for our Ocado delivery. Plenty of time available, given they arrived over an hour later than their billed time. A tiny bit of the logic was tidied up, and I added a year-view. Overall, it’s now the dog’s bollocks, if I may say so.
Below is the logic I used, in English rather than Excel.
The first thing to do was to confirm the weekday of the first day of the year. So I took the value from concatenated the year on to "01/01/", and calculated its weekday from there.
Next, I created a table showing each of the month-lengths, eleven of them hardcoded, February’s using the slightly cumbersome formula for working out whether a year leaps: divisible by 400 is; divisible by 100 is not; divisible by 4 is; otherwise, not. The table also contains the weekday of the first of each month, calculated as the mod base seven of the previous month’s start date plus that month’s length less one.
On the calendar sheet itself, the first row of January’s dates was created by comparing the number of the day of the week each cell represented with the day the month started on to establish whether to show a blank (the month hasn’t yet started) or a number, either a 1 for the first day, or the previous cell plus 1 for subsequent days.
The rest of the rows’ entries simply compare the previous entry with the number of days in that month. If the two are equal, then that and subsequent entries display blanks; if not, then it’s time to increment the previous entry by one.
Each month has six rows of entries to accommodate the rare months that start late in the week and drip a day or two into week six.
And the cells’ formats are dependent on their contents and the weekday. Blank cells have no borders; those representing weekends are shaded with borders; those representing weekdays are clear with borders.
Combining so many beautiful elements of Excel into a spreadsheet so useful was rewarding to say the least. Formula-driven conditional formatting, lookups, ifs, weekdays, moduli. Happy days.
Comments
Leave a Reply