Calculating Difference Between Two Times

  • Hello all, I am trying to calculate the difference between a scheduled start time and an actual start time. If the actual start time is greater than the scheduled start time a negative time (hh:mm:ss) should be returned. instead the cell is populated with #############....


    I have tried to reformat using the custom formats, but the only options in excel 97 are for either standard numerics or £ (these return a minus figure).


    I've had a look at the time functions but could not see a suitable one.


    I'm sure there is a simple format solution to this.


    :confused:

  • Re: Calculating Difference Between Two Times


    Hi


    I suspect that even with the Column width set at 255 all you will see will be #####..


    You could try a conditional formula like


    =IF(A2>A1,-(A1-A2),(A1-A2)


    which removes the problem of the ###s but shows a positive solution whereas the result is negative.


    The only way I can think of to get around this would be by VBA


    Code
    If Range("A2") < Range("A1") Then
    Range("A3").Formula = Range("A1").Value - Range("A2").Value
    Else
    Range("A3").Formula = -(Range("A1").Value - Range("A2").Value)
    Range("A3").Interior.ColorIndex = 3
    End If


    which still shows a positive value but highlights the cell in red, (I think)
    HTH


    Robert

  • Re: Calculating Difference Between Two Times


    This is one butt-ugly formula but it works without having to use VBA. If negative, the result is text so cannot be used for calculations in other formulas.


    =IF(A1>A2,A1-A2,CONCATENATE("-",HOUR(A2-A1),":",IF(MINUTE(A2-A1)<10,"0",""),MINUTE(A2-A1),":",IF(SECOND(A2-A1)<10,"0",""),SECOND(A2-A1)))


    If the time is negative, you have to build up your own text string; the FORMAT function does this very nicely in VBA but is not a spreadsheet function. That's why we need all the HOUR/MINUTE/SECOND functions. If you don't do that, you get a negative fractional number that is the time serial number.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!