Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Let's play-What's wrong with my formula-Numbers (pic)

Julien

Julien

macrumors G4
Original poster
Jun 30, 2007
11,875
5,454
Atlanta
Simple spreadsheet and need to keep a cumulative time total. Adding time works in Excel but I can't figure out how to add time in Numbers. What I'm I doing wrong and what is the correct format and/or formula I need?

screenshot_27.jpg
 
thecardyman said:
Have you tried setting the cell format to Duration?
Click to expand...

Thanks, Duration is the key format. However when I imported from Excel I have about 65 1 month sheets that are all this way. Is there a way to copy to new column, format or otherwise change the absolute value. For instance I have 1:05:00 in B6 and if I format it to Duration I get 05/05/2010 1:05:00AM when I need it to be 1h 5m 0s.

EDIT: Also when there is no value (days I don't do cardio) the cell is empty and it comes up with the blue triangle in the top left cell corner. If I click on it says: Duration units were removed OK. Is there a way to set a rule so it ignores empty cells (or treat them as 0h 0m 0s)?

screenshot_28.jpg
 
Am I missing something here, or are you trying to add the cell to the left (Time 'May 3, 2010' or cell B4) to the total of the "Total Time for Month" column, and the formula is being entered in cell C4, which is in the "Total Time for Month" column? If so, then you have a self-reference error, because you're referencing the cell containing the formula in the formula, which can't be done. You need to tell it to calculate just the cells above it, not the cell it's in.

If I misread, then I apologize, because I probably don't know what the answer would be.

jW
 
This assumes that the "date" portion of column B (Time) is the same as the date portion of column A (Date).

Try setting the "time" portion of column A (Date) to 00:00:00 (if it isn't already) (but set its format to just display the date portion) and setting column c=column B (Time) - column A (Date) and set the format of column C to Duration (0:00:00). Then set column D (Total Time for Month) as column C + previous row from column D.
 
Mal, you've almost understood what Julien's trying to do. He's trying to add the value of "Total Time for Month" from the previous row to the Time from the current row to get a running total for the month.
 
thecardyman said:
Mal, you've almost understood what Julien's trying to do. He's trying to add the value of "Total Time for Month" from the previous row to the Time from the current row to get a running total for the month.
Click to expand...

Yeah, glanced back at it and now I see he has referenced the previous cell, not the whole column. Makes sense.

Which means I have no clue as to the actual solution. Good luck though!

jW
 
thecardyman said:
This assumes that the "date" portion of column B (Time) is the same as the date portion of column A (Date).

Try setting the "time" portion of column A (Date) to 00:00:00 (if it isn't already) (but set its format to just display the date portion) and setting column c=column B (Time) - column A (Date) and set the format of column C to Duration (0:00:00). Then set column D (Total Time for Month) as column C + previous row from column D.
Click to expand...

Trying to understand but can't. What I want is to move or copy column B's dates and time format values into column C as a Duration. Example: take B8 date and time format value of 5/7/2010 12:58:32AM (it shows as 0:58:32 but the cell's value is 5/7/2010 12:58:32) and move/copy to C8 Duration formated value cell as 0h 58m 32s. Is there any way to make 5/7/2010 12:58:32AM=0h 58m 32s?

Also Column A format is Date and Time (Time None), B Date and Time (Date None), of course the new column C is Duration and D (which contains the formula) has been changed to Duration.

A and B are NOT directly related. A is the date I did the cardio. However if I didn't enter until the next day B has different date. For instance A11 has 5/10/2010 while B11 has 5/11/2010 0:59:07 (I entered my info the day after). Sometimes there can be several days difference between A and B.

I want column A to stay like it is too since it is the date completed and not part of the Duration time.
 
OK, rather than use column A, I think (I'm not at my Mac at the moment so can't confirm) there is a function called DATEVALUE which returns just the date portion of a date/time. So you could set C11 = B11 - DATEVALUE(B11) which should result in column C being set to just the time portion of column B.

e.g. 05/05/10 01:30:00 - 05/05/10 00:00:00 = 01:30:00

There may even be a function called TIMEVALUE, in which case C11 = TIMEVALUE(B11).

Again, format column C as Duration.

Hope this works this time.
 
thecardyman said:
OK, rather than use column A, I think (I'm not at my Mac at the moment so can't confirm) there is a function called DATEVALUE which returns just the date portion of a date/time. So you could set C11 = B11 - DATEVALUE(B11) which should result in column C being set to just the time portion of column B.

e.g. 05/05/10 01:30:00 - 05/05/10 00:00:00 = 01:30:00

There may even be a function called TIMEVALUE, in which case C11 = TIMEVALUE(B11).

Again, format column C as Duration.

Hope this works this time.
Click to expand...
Thanks for helping but not quite there. I believe TIMEVALUE is what I want since I'm only interested in the time value. However the result is a 16 place decimal number (Duration format returns a 0). How do I convert this to the Duration time?

Also when I use DATEVALUE I get 0:00:00 as in cell C4.

screenshot_34.jpg
 
OK, I think I've sussed this now. The problem seems to be that when you format a date/time column to just display the time, the date info is lost.

Code: 
1 May 2010 01:02:03	 1 May 2010 00:00:00         1:02:03
           01:02:03	22 May 2010 00:00:00      -502:57:57

In the above example the first column is the same value (01/05/2010 01:02:03) just formatted differently. The second column is set to DATEVALUE(first column). As you can see, by just not displaying the date the value of the second column changes!!! The third column is the difference in the other 2 columns (column 1 - column 2), formattted as Duration

The solution - you need 2 extra columns. Firstly, format your existing Time column to display the date and time. The first extra column needs to be set to the DATEVALUE of your Time column. This should give you a column with the same date as your Time column with a time of 00:00:00. Format it to show date and time. You then need to set a second extra column = Time column - first extra column. This should now show the time only from your Time column. Format it as Duration. Use this second column in your calculation for Total Time for Month.

You can hide your original Time column as well as the first extra column.

I'd include a nice picture showing it working (as you have done) but I don't know how to take a snapshot of part of the screen.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back