Excel – Calculate Hours and Minutes Between Two Time Periods

Try this formula on A3:

=TEXT(A2,"00\:00")+(TEXT(A1,"00\:00")>TEXT(A2,"00\:00"))-TEXT(A1,"00\:00")

It should also account for durations that cross midnight (see example below). You can enter times in A1 or A2 without colons.

In the example below, A1 contains the Start time and A2 contains the End time.

Number Formats used:
A1: Custom > 0000
A2: Custom > 0000
A3: Custom > [h]:mm or h.mm (based on your example)

If you want to compute for hour fractions instead (i.e. 2.50 hours instead of 2:30), set A3's number format to General and then change the formula to:

=24*(TEXT(A2,"00\:00")+(TEXT(A1,"00\:00")>TEXT(A2,"00\:00"))-TEXT(A1,"00\:00"))

One option would be to use a combination of cell formatting with the Excel TIME functions.

Format your time input cells (A2 and B2 in my example) as Text. The format expected will always be hhmmss, so enter the leading zero for times with single-digit hours. Then you can calculate with this formula:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

This grabs the left-most 2 characters as the 'Hours', the middle 2 characters as the 'Minutes', and the right-most 2 characters as the 'Seconds' and converts them to what Excel recognizes as a time. It then subtracts one from the other, and displays the result, with the formatting hhmmss:

EDIT: Seeing as the requirement isn't quite as specified in the question, I've amended the formula to take account of leading zeros by padding it out:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

Horribly less readable, but now pads the value with zeros and uses the right-most 6, so should work regardless of how many zeros you use.

I believe you'll actually want to special format the result as hh:mm:ss in this case.