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

Discussion in 'Mac Apps and Mac App Store' started by Julien, May 20, 2010.

  1. Julien macrumors G3

    Julien

    Joined:
    Jun 30, 2007
    Location:
    Atlanta
    #1
    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?

    [​IMG]
     
  2. thecardyman macrumors newbie

    Joined:
    May 20, 2010
    Location:
    Wirral, England
    #2
    Have you tried setting the cell format to Duration?
     
  3. Julien thread starter macrumors G3

    Julien

    Joined:
    Jun 30, 2007
    Location:
    Atlanta
    #3
    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)?

    [​IMG]
     
  4. Mal macrumors 603

    Mal

    Joined:
    Jan 6, 2002
    Location:
    Orlando
    #4
    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
     
  5. thecardyman macrumors newbie

    Joined:
    May 20, 2010
    Location:
    Wirral, England
    #5
    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.
     
  6. cawesjmu macrumors 6502

    Joined:
    Apr 4, 2004
    Location:
    Richmond, VA
    #6
    If the MR community can't help, try the Excel Forums. They also have some wicked smart people who are excel gurus.
     
  7. thecardyman macrumors newbie

    Joined:
    May 20, 2010
    Location:
    Wirral, England
    #7
    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.
     
  8. Mal macrumors 603

    Mal

    Joined:
    Jan 6, 2002
    Location:
    Orlando
    #8
    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
     
  9. Julien thread starter macrumors G3

    Julien

    Joined:
    Jun 30, 2007
    Location:
    Atlanta
    #9
    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.
     
  10. thecardyman macrumors newbie

    Joined:
    May 20, 2010
    Location:
    Wirral, England
    #10
    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.
     
  11. Julien thread starter macrumors G3

    Julien

    Joined:
    Jun 30, 2007
    Location:
    Atlanta
    #11
    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.

    [​IMG]
     
  12. thecardyman macrumors newbie

    Joined:
    May 20, 2010
    Location:
    Wirral, England
    #12
    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.
     
  13. Julien thread starter macrumors G3

    Julien

    Joined:
    Jun 30, 2007
    Location:
    Atlanta
  14. thecardyman macrumors newbie

    Joined:
    May 20, 2010
    Location:
    Wirral, England
    #14
    No probs. I'm trying to be a fan of Numbers (and Pages and Keynote) but it's problems like this that make me want to stick with Microsoft Office.
     

Share This Page