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.


  • Re: Calculating Difference Between Two Times


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

    You could try a conditional formula like


    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

    If Range("A2") < Range("A1") Then
    Range("A3").Formula = Range("A1").Value - Range("A2").Value
    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)


  • 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 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!