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

Format changes from Duration to Automatic and I cannot change it back.

I am adding a quarterly summary table and drawing data from separate weekly tables. My data is formatted as Duration, but when I add the cells to the formula, the total cell changes formatting to Automatic. I have tried to change the formatting back to Duration but Numbers will not let me. Now my data is a decimal number instead of an 0H 0M number. What did I do wrong and how can I fix it?

iMac, OS X El Capitan (10.11.1)

Posted on Nov 24, 2015 8:21 PM

Reply
6 replies

Nov 26, 2015 5:05 PM in response to Yellowbox

Thank you for responding Yellowbox.

The formula in the quarterly table is a simple = formula from one cell in thirteen different tables (Q4 2015 Driving Log::WK1::Total Time 'Totals:'+Q4 2015 Driving Log::WK2::Total Time 'Totals:'+Q4 2015 Driving Log::WK3::Total Time 'Totals:'+Q4 2015 Driving Log::WK4::Total Time 'Totals:'+Q4 2015 Driving Log::WK5::Total Time 'Totals:'+Q4 2015 Driving Log::WK6::Total Time 'Totals:'+Q4 2015 Driving Log::WK7::Total Time 'Totals:'+Q4 2015 Driving Log::WK8::Total Time 'Totals:'+Q4 2015 Driving Log::WK9::Total Time 'Totals:'+Q4 2015 Driving Log::WK10::Total Time 'Totals:'+Q4 2015 Driving Log::WK11::Total Time 'Totals:'+Q4 2015 Driving Log::WK12::Total Time 'Totals:'+Q4 2015 Driving Log::WK13::Total Time 'Totals:'). Here is the table I am having trouble with.



Total Miles

Total Time

Total Fuel Gallons

Total Fuel Dollars

Q1

0

0

0.000

$ 0.00

Q2

0

0

0.000

$ 0.00

Q3

0

0

0.000

$ 0.00

Q4

1,144

80.7333333333333

46.696

$92.33

YTD

1,144

80.7333333333333

46.696

$92.33


The total time should be 80 H 44 M. It displays properly in the Quarterly table as a Duration format. No matter what I try and do, it defaults back to Automatic here (C5 and C6; probably C2, C3 and C4 also).

Nov 27, 2015 2:06 AM in response to croy1776

Hi croy,


When I enter H and M (Column B) Numbers interprets the cell as Text

User uploaded file

Using plus in B1, = B3+B4 the red error triangle tells me :

User uploaded file

The zero in B2, =SUM(B3:B4) is because SUM ignores text values.


When I enter h and m (Column C) Numbers interprets that as Duration. and the plus and SUM formulas work.

What values are in the thirteen tables?


Regards,

Ian.

Nov 27, 2015 5:03 AM in response to croy1776

If any of those cells being added up are in decimal form, the cell with the addition will have the duration units removed (i.e., will be in decimal form) and it will have a blue warning triangle in the upper left corner of the cell and you will not be able to change it from automatic to duration. So I suspect there is a problem in one of your other tables. One probably has a decimal number in the cell instead of a duration.

Nov 27, 2015 5:17 AM in response to croy1776

Adding up(or other operations on) cells from multitudes of tables can be error prone, especially if you often make new tables and need to edit all your formulas to include the new tables. Here is a helpful hint to make it easier. Make a new table that pulls in the data you need from all the other tables. That way it is all in one place. You can include extra rows for tables you will add in the future.


User uploaded file

Columns A and B are text that you enter (much of which can be "filled down" vs entering it all manually)

Row 1 is text you enter.

Cell C2 =IFERROR(INDIRECT($A&"::"&$B&"::"&C$1),"")

Fill that formula across and down to complete the table.


You can do more sophisticated things than this. The point is for the cells in this table to contain the necessary data from the other tables so you can easily use it in a "summary" or "main" table.

Format changes from Duration to Automatic and I cannot change it back.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.