Using NUMBERS

Here's a tip John Carter has discovered regarding NUMBERS. He begins, "Let's say you have two DATE columns (formatted for dates). Let's call them Start and Finish.

"Now let's say you want to know the number of months between these dates, and you want the result in Integer format to a two decimal precision.

"The standard way of subtracting the two dates gives a result that looks like an Integer, but it is actually a DURATION format. You can't operate on a DURATION value in the same way that you can operate on an Integer and expect an Integer value, because what you get will still be in DURATION format.

John continues, "For instance, let's say that the Start value is 1/1/2009 and the Finish value is 10/1/2009. The difference (=Finish-Start) will be 273 days, and it will appear as 273d in the cell. The 'd' stands for days, not duration. Now if you want to find the number of months as an Integer you might think that just dividing by 30 (or by (364/12) to be a little more accurate) would give you the number of months in Integer format, but it doesn't. Look:

"The manner in which Months is displayed depends on how many places you want to show in the result (using the Inspector). But notice that the result appears to be 9 days, given by the '9d' value. This is actually supposed to be read as 9m, because it's months, not days, but Apple didn't think ahead on this one and didn't provide the Inspector with the ability to format a duration in months, and if we set the display for weeks, we get 1w 2d 0h. Well, fortunately that equates to 9d. Oh, what a mess! It's very awkward in this format to read properly, so let's change the value of Days to an Integer format.

"With Microsoft Excel, you could simply change the Days cell format to be an Integer and be done with it. NUMBERS doesn't allow changing the format of a DURATION in such a direct fashion.

In the Days cell where you are subtracting the two dates, e.g., "=Finish-Start", use the following formula instead:

=DUR2DAYS(Finish-Start)

This converts that value to an integer.

Then in the next column when you divide Days by (364/12), you get a real Integer instead of a duration that doesn't even look right.

John concludes, "We could have used DUR2DAYS() on the Months value. It will amount to the same Integer value."   ###