Excel – Enter time without colons (hhmmss) and calculate time differences in Excel

microsoft excelworksheet-function

I want to enter the time without colons as hhmmss and calculate the time differences with another cell. For example I enter the beginning time in one cell and the ending time in another. Then I need to calculate the time difference. But it has to include hours, minutes and seconds.

The complication is that I want to enter the times without a colon but see it with the colon in the cell. To do this I formatted the cells with custom number format 00\:00\:00.

Best Answer

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:

enter image description here

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.

Related Question