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 behhmmss
, so enter the leading zero for times with single-digit hours. Then you can calculate with this formula: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:
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.