Scroll the menu to find all the places to visit
24 Hour Clock Calculations in Excel
28 Dec 2011Recently I’ve been doing some work with Excel to carry out some time calculations based on the results of an automated job. The job is sometimes triggered one night and finishes in the early hours of the next morning. After loading the figures into Excel I discovered that time calculations aren’t always as simple as subtraction when times cross midnight. A quick test later and I had a workable solution that I thought might be useful to share.
The screen shot below shows some simple time calculations subtracting the second time (T2) from the first time (T1). As you can see the first and second rows of times calculate correctly. The third row however shows up as a series of hashes.
Hovering over the cell containing the hashes produces a pop-up that reveals the result may be a negative result - which in this case would indeed be true.
Selecting the absolute value would yield the same result as the first calculation (3:45), but in reality the time elapsed between these two times is 20:15.
The solution is just to use a conditional test to check if T2 is bigger than T1. If it is then a normal time calculation will suffice. If it isn’t then the time difference would be equal to midnight minus T1, plus T2. For cells A4 (T1) and B4 (T2), this can be expressed as the following Excel formula.
=IF(B4>A4,B4-A4,24-A4+B4)
This results of this formula are shown for each pair of timings in column C in the screen shot below.
This would have to be replicated for each time calculation. If you have many calculations and perhaps might want to include additional calculations it may make things easier to use a VBA function to carry out this calculation. The function below does exactly that.
Function Elapsed(tmStart, tmEnd)
If tmStart <= tmEnd Then
Elapsed = tmEnd - tmStart
Else
Elapsed = 24 - tmStart + tmEnd
End If
End Function
