Search
« Clearly I need a new mobile web browser | Main | Automating Mail-ins to Evernote from GMail »
Wednesday
Dec282011

24 Hour Clock Calculations in Excel


Recently 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

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (6)

I HAVE BEEN TRYING EXCEL FORMULAS IN TIME CALCULATIONS AND I HAVE ALWAYS ENDED UP WITH THE WRONG ANSWER.
YOUR ANSWER IN THE THIRD ROW IS INCORRECT AS IN TIME CALCULATION, IS THE SAME AS IN NUMBERS EXCEPT THAT MINUTES = 60 SECONDS.
IF YOU TAKE 10:15PM MINUS 6:30AM; THE ANSWER SHOULD BE 7:45.
TAKE 12 HOURS, (TO MIDNIGHT) - 10:15 = 1:45 + 6:30 = 7:45

SOMEHOW, EXCEL KEEPS ENEDING UP WITH THE WRONG ANSWER !!

June 26, 2013 | Unregistered CommenterFaizel G. Hassen

Hi.

Just wondering if anyone has any ideas on how to represent a full day EXACTLY (ie from 06:30 one day through to 06:30 the following day - or even more challenging, from midnight one day through to the next one) in Excel (2010) when subtracting one time from another.

Everything I've tried has returned 00:00, when in reality it's obviously the full 24:00 hours. Appreciate any help!

December 3, 2013 | Unregistered CommenterPeter Mc

Faizel.

The calculation is intended to tell you the elapsed time from column A to column B - i.e. it is directional. For the third row this would be the time elapsed between 10:15 (AM on day 1) to 06:30 (AM on day 2) - note the time's are expressed using 24 hour notation.

From 10:15 to 11:00 is 0.75 hours (45 mins).
From 11:00 to 12:00 is 1.00 hours.
From 12:00 to 00:00 (24:00) is 12 hours.
From 00:00 to 06:30 is 6.50 hours.

0.75 + 1 + 12 + 6.5 = 20.25 hours = 20 hours and 15 minutes (20:15).

So the answer in the third row is as I'd expect it to be.

With your example of 10:15 PM (22:15) to 6:30 AM (06:30)...

From 22:15 to 00:00 is 1.75 hours.
From 00:00 to 06:30 is 6.50 hours.

1.75 + 6.5 = 8.25 hours = 8 hours and 15 minutes.
Your corresponding calculation of 1:45 + 6:30 = 7:45 is actually too low (by 30 minutes).

When I use the formula above and these times I get a result of 08:15.

Hopefully that clarifies the calculation for you.

Should you just want the difference between two times on a 24 hour clock without directionality (i.e. always assume on same day) then this should suffice for the above (row 2):

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

I hope that all helps.

March 2, 2014 | Registered CommenterStephen Millard

Peter Mc.

Excel's issues in these situations is that if you don't specify a date with the time then it won't know that it is a different day.

A crude solution would be to add another if function and say if they are the same then output 24 hours. A better solution is to specify the date along with the time.

The date time can be specified in the cell but set to display (via formatting options) as just the time. The date part remains for editing only. Formatting of time is typically done using "hh:mm", but by adding some square brackets around the hours we can tell Excel to work beyond 24 hours (it's usual modulo basis for time calculations) - "[hh]:mm".

I've uploaded a spreadsheet with some examples for you.

Regards,

Stephen.

March 2, 2014 | Registered CommenterStephen Millard

This is just what i've been after. Prefect.

Just one question tho. If i wanted to add up the 3rd column to show Total hours. How would i do this?

Thanks

Patrick

July 7, 2017 | Unregistered CommenterPatrick

Patrick.

I think you would just need to use =SUM() across the column contents and then be sure to set a date/time format of [hh]:mm.

March 28, 2018 | Registered CommenterStephen Millard

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>