Calender template in Numbers, start the week with Monday

What works here is this (assuming you are using the template for Numbers 3):

Add a column at the right by clicking the symbol by the arrow in this screenshot:

The dates in the new column will fill in automatically. Type SUNDAY in the header of the new column.

Then hide (don't delete) the original SUNDAY column on the left.

That's it!

The steps are similar for the little calendars upper right, except that to hide the original Sunday columns there you need to first unmerge the cells in the header of each of those tables (select the header and choose 'Unmerge Cells' from the Table menu). After unmerging, drag the formula in A1 over one cell to the right and then hide column A. And finally select the cells in the header and merge again.

A lot of the math for the calendar, if you're curious, is in hidden rows and columns in the table on the upper left that displays the date and year. Unless you are a lot better at calendar math than I am, best not to mess with it, though.🙂

SG

Hi vesterberg,

If you are willing to dip a bit into the nuts and bolts, you can get what you want by changing a single number and then changing your day designations.

Unhide the hidden rows in the Month and Year table. Change the "1" in A3 to a "2".

The dates on all the calendars slide right over. Rehide those columns, move the Day headings over and you are there.

quinn

Ian and SG,

Clever is the word for it! Don't even have to change a formula.

q

Hi quinn,

I have always found constructing calendars a challenge. Different days in each month, leap years, start the week on a Sunday or a Monday. Like vesterberg, I like to start on a Monday. Then Saturday and Sunday are nice and snug on the right (weekend events often run for two days).

Years ago, I did create an Excel "Year Planner" that was fully automatic (just enter the year and it calculated 1st January, it entered 1 in the correct cell, calculated following dates, the start of the next month and allowed for Leap Years). It took me several days and many convoluted formulas. It would not convert to Numbers, and I forgot how I did it, so I gave up. I now use a very simple year planner and manually update it each year.

Consult a "real" calendar such as the Calendar app on the Mac or a printed calendar and enter dates.

It's only a once-a-year job, and it allows me to see the year at a glance. Also easy to manually count days or weeks until an event.

Conditional Highlighting (change of month name, 1st of a month) does help with visual cues.

Regards,

Ian.

Well, realizing that my "list of things I don't quite understand" is already long enough, and armed with the discovery of "quinn's factor" in A3 and observations of the behavior I had observed when simply adding a column, I decided to have another look, and discovered ... the formulas are all simple, there's no modular arithmetic with MOD(), all quite simple really!

It simply constructs the first day of the month from the Popup choices for month and year and using a day of 1. It figures out what day of the week that is (which depends on q's factor) and also gets the last day of the month. That's it! (Rows 8 through 14 are for the previous month and next month calendars and make it all look more complicated than it is).

Then in the main calendar the "anchor" value in A2 is derived this way:

All that's doing is taking the first-day of the month date and adding (1-the day of the week of the first day of the month).

Then the other cells in the main calendar flow from that anchor value, cells to the right adding a day via =A2+DURATION(0,1), and cells below adding a week via =A2+DURATION(1)

The two little calendars are constructed in the same way. They're all using a "Day Only" custom Data Format.

And there's Conditional Highlighting to "fade out" the cells that are not in that month:

An interesting showcase for the power of Numbers. I find this and some of the other templates deceptively simple. More to them than meets the eye.

SG

I selected the four tables in the template, hit command-c to copy, then command-v to paste, and dragged the new set of tables below to form a new month. Then, when I enter information in the first month, it doesn't affect the next month.

Alternatively you could just duplicate the sheet via the dropdown menu up next to the sheet name in the "tab" at the top.

SG

Hi vesterberg,

I've already setup Monday to start the week in the general settings and in the Calendar app settings but that doesn't do much in Numbers.

Neither System Preferences nor Calendar app will change the Numbers Calendar Template.

I take my hat off to whoever created that Calendar Template for Numbers 3. It is very clever. I do not know how to answer your question, but here are some hints that may help you get started. Perhaps other Numbers users will stop by with ideas.

To see how it was constructed, click on the month (September in my version) or year (2014 in my example). They are in a table.

Menu > Table > Unhide All Rows.

Format Panel > Table > Table Name.

Click on the previous and the following months, and the current calendar. They are also tables.

Format Panel > Table > Table Name.

Now click around to see the formulas. The table names will help to show how the formulas work.

Also, have a look at Format Panel > Cell > Conditional Highlighting to see why some dates in the main calendar (last month, next month) are grey.

I realise this is not really helping, but it might get you (or another user) started.

Regards,

Ian.

Hi Ian,

I saw your post after I clicked the 'Reply' button. Certainly is a clever template, isn't it! I was counting on you or somebody else to go in and change a formula here or there and have the whole thing magically shift to Monday first, because I failed miserably in my attempts at that. Then I tried adding a column "just to see what would happen" ... and liked the results.

SG

Hi quinn,

It took me a while to follow your step 2

move the Day headings over and you are there.

Then I realised you meant the text (weekdays) in the main calendar and in the little Previous Month and Following Month calendars. I was trying to combine your brilliant solution with SG's brilliant solution. Two different solutions!

I see it now. A3 in Month and Year is the trigger. The weekday labels are just text.

Thanks!

Ian.

Hi Ian,

Sorry I wasn't clearer. I wrote mine before I saw SG's so it didn't occur to me they could be confused.

I don't feel like I really understand all of what they did with that calendar still. What I did notice is that every day is dependent on the one before and A2 has a unique formula that referers to the Month and Year table. Month and Year::$A$4 shows the first day of the month and year. Month and Year::$A$5 finds the WEEKDAY(date, first-day). and that pointed to A3. It was an interesting exercise is solving something without understanding all the steps between. The joy of a solution with the lingering unease of incomprehension. A great combination actually. If they really wanted to confuse us the could have made my so-called Day headings automatic and we could never have figured it out.

quinn

Hi Ian,

Date/Time, Duration I find the most "interesting" (read difficult). It seems you have to jump thru so many hoops to get what you want. Interesting that Apple used a lookup to tell it that January = 1 for instance. I would not be up to the challenge of building my own.

I am quite happy with the built in calendar on my Mac and Week Calendar on my iPod (actually love this app).

I always look forward to reading your take on people's questions.

quinn

I'm trying to do this in Numbers 3.6.2 - but I'm confused. Could you help? I have nothing to unhide, presumably a change with the newer version? But I'm looking at the formula you hav above & not managing to work out what I need to do with it. Am I in the right place to change week start to Monday? (be good to do it on the prev/next month mini calendars too)...

Thanks in advance

Hi Charlotte,

Thanks for this "blast from the past". I enjoyed rereading this discussion 🙂.

It is late here, and I must go to bed. But I look forward to SG and Quinn joining in once again.

Regards,

Ian.

Hi jjfermine,

Sometimes Apple templates seem to be more about how to do things than to have practical applications. I think this calendar template borders on that.

We are looking at a single table, the numbered days generated when the month is selected in the popup.

To get a years worth of calendar you need to duplicate this sheet 12 times and select the appropriate months in each tab.

quinn

edit: As SG says.

Charlotte Hattersley wrote:

I have nothing to unhide, presumably a change with the newer version?

You can unhide here:

SG

Sorry. If I add something in September, I don't want that "something" in every month of the year. Attached two screenshots.

Hope you understand.