Numbers cannot calculate time arithmetic, but Excel can do it.

Question

Numbers cannot calculate time arithmetic, but Excel can do it.

A calculation of time that works in Excel results in a bogus warning icon in Numbers.

Column A is a date-time format. Column B is a time format (hh:mm) with No Date. Column C is a date-time format like column A.

The worksheet is used to calculate elapsed time on time-slips, like employee hours. Column A is the starting time. Column C is the ending time. Column B is used to insert a "pause" interval, like for example going to lunch.

In Column D, the math is simple: C minus A minus B. I can then also have a column E than converts the hours to decimals, e.g. 4:30 as a time-displayed result translates to 4.5 hours, which can be used to multiply by an hourly rate to arrive at a dollar total. Right?

In the rows that have no pause time in column B, the calculation works perfectly. But in the rows that have a pause time, the warning says that Numbers cannot subtract a time from a number, or something like that. It's a bogus warning message because I'm not subtracting a time from a number. I'm subtracting hours (time) from a a calculated result that is formatted as hours (time).

Bottom line: it works in Excel. It should work in Numbers, too.

MacBook Pro, Mac OS X (10.6.4)

Posted on Jul 5, 2010 11:47 AM

Reply

Answer 1

Top-ranking reply

KOENIG Yvan

Level 8

41,819 points

Jul 5, 2010 11:53 AM in response to Dennis_Burnham

Dennis_Burnham wrote:

Bottom line: it works in Excel. It should work in Numbers, too.

As far as I know, Excel is not the clone of the "Tables of law".

I'm really glad when I see something which Apple doesn't make like M…Soft !

If you want applications behaving the M…Soft way, use M…Soft products !

Yvan KOENIG (VALLAURIS, France) lundi 5 juillet 2010 20:53:08

Reply

Answer 2

KOENIG Yvan

Level 8

41,819 points

Jul 5, 2010 12:16 PM in response to Dennis_Burnham

If you accept to use Numbers as it is designed to be, you may use the formula:

=DURATION(0,0,(C-A)-TIMEVALUE(B)*24)

A date_time value minus a date_time value is, as the warning told you, is a number.

It's logical to be unable to substract a date_time from a number. I read correctly even when what you write is wrong. Your cell in column B *_doesn't contain a time value_*. Such a value doesn't exist in Numbers.
We have only date_time values formatted to display only their time component.
It's well explained in the User Guides.

As you wish to have the calculated time value in decimal format in column E, I would use an other scheme:
In column D, I would insert :
=DURATION(0,0,E)
in column E, I would insert :
=(C-A)-TIMEVALUE(B)*24
or, to get rid of the blue triangle :
=(TIMEVALUE(C)-TIMEVALUE(A)-TIMEVALUE(B))*24
This formula is more coherent and has the advantage to be compatible with Numbers '08

To be fully compatible with Numbers '08, the formula in column D would be replaced by
=TIME(0,0,E*3600)

The compatible formulas are my preferred ones.

Yvan KOENIG (VALLAURIS, France) lundi 5 juillet 2010 21:13:48

Reply

Answer 3

Badunit

Level 7

28,240 points

Jul 6, 2010 6:01 AM in response to Dennis_Burnham

Dennis,

In Numbers, your column B needs to be formatted as Duration. Then you can do the subtraction. There is no "time" format in Numbers; there is "Date & Time" and there is Duration.

A = 3:00 AM July 6, 2010
B = 3:00 (Duration format, 3 hours)
C = 3:00 PM July 6, 2010

C-B-A = 9:00 (Duration of 9 hours)

Reply

Answer 4

Jul 6, 2010 6:14 AM in response to Badunit

The formatting of a cell to the "Duration" setting in the inspector does not, as Badunit suggests, work as it should. Helpful idea, but not a solution.

I'll try the other suggestions too.

Reply

Answer 5

Jul 6, 2010 6:19 AM in response to KOENIG Yvan

thank you

Reply

Answer 6

Badunit

Level 7

28,240 points

Jul 6, 2010 8:23 AM in response to Dennis_Burnham

Odd that it did not work for you. I would not have suggested it had I not tried it first myself. The example I gave was the exact one I used in a Numbers '09 table.

My guess is that you have a Date & Time value in the cell in column B. Numbers has a quirk when it comes to formatting. If the cell has a value in it and you attempt to change the format to something that it cannot be changed into (like taking a cell that has a date & time and trying to reformat it into a Duration), it will look like the reformatting worked but it will not have actually happened. Delete the contents of the cell first, then change the format.

Message was edited by: Badunit

Reply

Answer 7

KOENIG Yvan

Level 8

41,819 points

Jul 6, 2010 12:12 PM in response to Badunit

Hello Badunit

I guess that the OP was fooled by the way you passed the time value;

You wrote :

B = 3:00 (Duration format, 3 hours)

alas,

(a) if I enter 3:00 in a cell then set its format to duration, with my french system (10.5.8) I get:
06/07/2010 mardi 6 juillet 2010 03:00:00

(b) if I define the cell as duration then enter 3:00, I get :
3h 0m 0s

So itsn't really surprising that the OP was fooled.

Happily, your late message gave the 'missing info' : define the format before entering the value.
As I have often to work on imported datas, I can't rely upon this requirement.
It's one of the reasons why I often use TIMEVALUE which is a neat and efficient way to extract the time value without changing the imported cells.

An other scheme which I also use is a script which extract the cell's content, define the format, insert the time value.

Yvan KOENIG (VALLAURIS, France) mardi 6 juillet 2010 21:12:19

Reply