How do I turn off automatic formatting of values in Numbers?

Question

Level 1

53 points

How do I turn off automatic formatting of values in Numbers?

I'm trying...trying...to use Numbers and have been stymied by its 'automatic formatting' feature. How do I turn it off?!

I'm creating a simple spreadsheet calculation for loan payments and interest. Numbers dutifully will calculate the 'days between dates' by entering a formula taking the newest date and 'subtracting' the older date. For example, it determines 11/1/15 minus 10/4/15 as "28d" for "28 days." But when I use that value in another formula (e.g., 28 days divided by 365 days X my interest rate times principal outstanding), it returns a 'time' value. No amount of formatting by me seems to override Numbers' desire to treat my interest value as a duration.

(In Excel, it does not format unless you specifically format the cell; Numbers seems to assume it knows what I want, but doesn't allow me to override. I would be perfectly happy to simply turn off the automatic formatting feature and format the cells manually.)

iMac (24-inch Early 2009), OS X Mavericks (10.9.2)

Posted on Oct 12, 2015 7:50 AM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Oct 12, 2015 8:56 AM in response to Fred Thorne

you can convert the duration value to a decimal value by using the formula:

assuming C1 contains the duration value, use:

=dur2days(C1)

Reply

Answer 2

SGIII

Level 8

35,216 points

Oct 12, 2015 10:03 AM in response to Fred Thorne

Treatment of dates and times is one of the areas where Numbers differs most from Excel.

"Durations" can be very be very convenient for adding and subtracting dates and times. However, they usually need to be converted to decimal values before use in many formulas involving multiplication.

Check out the Duration Functions section in Help > Formulas and Functions Help or here.

SG

Reply