Apple Event: May 7th at 7 am PT

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Level 1

0 points

Calendar Template Curiosity

Hello all. I have been beating my head against the wall with this one. The new Numbers is certainly a mixed bag. Personally I love it but there are some features that I'm sure most of us wait, with bated breath, to be built back into the program.

I learn a lot about the mathemagical inner workings of the spreadsheet by reverse-engineering the Apple-supplied templates to learn good practices and pick up some tips and tricks. But this week I cam across something of a conundrum.

One of the new templates included in both the OS X and iOS versions of Numbers is this Calendar template. It's elegant, simple and the formulae are purposeful. But in picking it apart and rebuilding it I noticed the Month pop-up cell must be formatted in a peculiar way or something. When I set out to remake this sheet, it was one of the first cells I made and formatted. A simple pop-up with the months of the year listed. But as soon as I began typing in formulae, I noticed it produced an error in calculations. I checked and re-checked everything looking for trailing or leading spaces in my value list for the pop-up formatting.

So I performed a simple test.

I made a new table with two pop-up cells. Each with the names of the months; January is listed through December. One is a copy and paste from the original Apple template and the other is my own pop-up. Both are formatted properly and identically. In a cell below I created a formula (IF A1=B1, "Yes", "No") to test the actual values driving the calculation. Without knowing what it is that causes it, the test shows the Apple's January ≠ my January. As soon as I copy and paste another one of Apple's month pop-ups into that cell, then and only then does A1=B1.

Would anyone care to investigate this template and help me understand what is causing the inconsistency?

Posted on Feb 17, 2014 8:39 AM

20 replies

Feb 17, 2014 11:16 AM in response to Badunit

OK. I see now. The fill date is embedded in the pop-up and was in the year that Apple created the document. So the "correct" way is to get the month out of that pop-up using the function month().

like this:

=IF(MONTH(A1)=MONTH(B1),"Yes","No")

Badunit

Level 7

26,014 points

Feb 17, 2014 11:20 AM in response to Wayne Contello

That is how it appears. I recall this was a problem in Numbers '09, too. Remember the Timesheet template where the time of day popup had a hidden date attached to all the times.

Another solution is IF(A1&""=B1&"","Yes", "No") . Your way iusing MONTH is clearer, though.

Feb 17, 2014 1:31 PM in response to Badunit

Ultimately, the Apple-designed template has a built-in time bomb in which if you use the template after the year has changed (from when Apple created the template) then the template will not work. We can fix the template using the formula from earlier and save an Improved Calendar template

Volvoguy Author

Level 1

0 points

Feb 17, 2014 1:58 PM in response to Wayne Contello

Thanks! Something 'dirty' is indeed embedded in the original template's formatting for that cell. Letting the CLEAN function chew on the data, it allows a user-created list to be used in the hidden cell's formula in A4.

Now my ears are perked up and I want to begin searching for other hidden or custom code within Apple templates.

Barry

Level 9

59,861 points

Feb 17, 2014 9:20 PM in response to Wayne Contello

Recalling the Employee Timesheet N '09 template, after reading the first page I was pretty much convinced the Month name in Apple's pop-up was a Date and Time value, showing only the Month(name), and suggest trying three cells with these formulas:

=DAY(A1)

=MONTH(A1)

=YEAR(A1)

Replacing A1 with the cell containing the pop-up menu (or copying the menu cell, then pasting it into cell A1 on the table with the test formulas.

But I see you got to that on Page 2. 😉

Regards,

Barry

Calendar Template Curiosity