# 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

Assuming you have the column wide enough for the result, can you attach a small sample workbook with the problem, please?

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