Skip navigation
This discussion is archived

Numbers: Cell time calculation not adding the correct time

8938 Views 5 Replies Latest reply: Dec 20, 2010 2:32 PM by KOENIG Yvan RSS
Skip68 Calculating status...
Currently Being Moderated
Nov 15, 2008 11:48 AM
Greetings,

First, thank you in advance for your assistance. I have searched the board and where there are many great formulas, I have not found the answer to this challenge.

Cell A1 has a time for example 5:45 PM

Cell C1 has an amount of minutes for example 40

Cell B1 should be adding 40 minutes to 5:45 PM for result of 6:25 PM

Cell A2 picks up the value of Cell B1 as its starting value so that it can calculate the same formula for the next line.

This document is a schedule so that it adjusts the times based on the amount of minutes a segment is. I can post an excel file if that will assist, the excel formula for this in the B cell is A1+TIME(0,C1,0)

Again thank you in advance for any assistance in solving this challenge.

John
Mac OS X (10.5)
  • Moich Calculating status...
    If A2 contains a time value such 5:45
    and B2 contains a number which= an amount of minutes ie 40
    If you want to add the minutes in B2 to A1 and return a time value (ie 6:25) then the formula in C2 is

    =TIME(0,(HOUR(A2)*60MINUTE(A2)B2),0)

    You'll note I've reversed the locations of B1 and C1 as per your example. You'll have to flip them round again if you want Time, Result, Minutes

    Hope this helps

    M.
    MBP 15" 2.5 Ghz, 4GB Ram. PB 17" G4 1.67 Ghz 1.5GB RAM, Mac OS X (10.5.2), 20" CD, Time Capsule 500 GB, Maxtor III 250GB, Ice Warrior 80GB, Inspire 1394
  • Moich Level 2 Level 2 (475 points)
    To add a bit: If you want A3 to pick up the value in C2 you can do one of two things:

    in cell A3 you could obviously enter the formula:

    =C2

    However if you want to keep everything blank until you enter numbers in the B column then you could put the following in A3:

    =IF(ISBLANK(B3),"",C2)

    Which will only return the value C2 if you enter something into B3 (your new minutes value)

    and then in the C column you could enter the equivalent condition:

    =IF(ISBLANK(A3),"",(TIME(0,(HOUR(A3)*60MINUTE(A3)B3),0)))

    eveything is now empty until you enter in an amount of minutes.

    Pull both these coloumn down the list and you should be going at least someway towards being happy.

    Apologies if over complicating

    M.
    MBP 15" 2.5 Ghz, 4GB Ram. PB 17" G4 1.67 Ghz 1.5GB RAM, Mac OS X (10.5.2), 20" CD, Time Capsule 500 GB, Maxtor III 250GB, Ice Warrior 80GB, Inspire 1394
  • Level 8 Level 8 (41,760 points)
    Skip68 wrote:
    Cell A1 has a time for example 5:45 PM

    Cell C1 has an amount of minutes for example 40

    Cell B1 should be adding 40 minutes to 5:45 PM for result of 6:25 PM



    The lazy guy which I am wrote this short formula:

    =TIME(HOUR(A),MINUTE(A)+C,0)

    Yvan KOENIG (from FRANCE vendredi 4 juillet 2008 11:38:08)
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
  • Level 8 Level 8 (41,760 points)
    =TIME(HOUR(A),MINUTE(A)+C,0)


    • Every time I may use them, I uses the "short references", those with no row num embedded.

    • The TIME function requires three parameters,
    a number of hours,
    a number of minutes
    a number of seconds

    but the formula parser is fair enough to understand that, when the given number of minutes is greater than 59, it means some hours + some minutes.
    And of course, mutatis mutandis, it's the same for the number of seconds.

    This is why, when I want to display with the current format hh:mm:ss a decimal time value as those resulting of calculations, I don't spent time to isolate the number of hours, the number of minutes, the number of seconds. I just calculate the total number of seconds.
    if cell B2 contains 0.1234490740741
    I type =TIME(0,0,B24*6060) in cell C2
    Then, applying the yy:mm:ss format I get 02:57:46
    inserting =TIMEVALUE(C) in D2, I get 0.1234490740741

    I know that this behavior is not described in the Help.
    I don't feel that it's a sin.
    It's for passing this kind of details that the forum exists.
    I feel perfectly odd questions whose response is available in the Help but it's always a pleasure to help users to discover features which are only available "between the lines".

    Yvan KOENIG (from FRANCE vendredi 4 juillet 2008 14:48:21)
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.