Pad zero to left of [h]:mm:ss cells if no number already present 1

[haru2002]

I would like to pad a zero to the left of a time value if no number is already present to the left of the ":".

I am importing a lot of data from another application. The data is in the format [h]:mm:ss. However, when the time value is less than one hour, nothing appears to the left of the leading ":". (for example :05:12 for 5 minutes and 12 seconds.) When the value is above one hour, there is no problem (example: 2:34:12).
I need to have a leading zero so I can total the time values. Doing this manually is not an option due to the large volume of data.

Thanks for any help you can give.
Regards,

 

[Nareik]

Try [hh]:mm:ss.

It will give you two zero's, but it may suffice

 

[haru2002]

Thanks, just tried it by painting the [hh]:mm:ss format on the data. It adds a zero if there is already a figure in the hour section, but if there is nothing there, it remains as is.
Did I do something wrong here?

 

[Nareik]

Try this additional step, it may make my previous advice redundant.

Look at Tools>options>view>display zero values checkbox.
Ensure that the checkbox is set.

That may toggle the view if there is 0 in the cell. If the cells is empty, I cannot find an immediate soultion other than filling those cells with a zero.

 

[GlennUK]

Importing a time in a format like :05:12 for 5 minutes and 12 seconds, will actually import as text.

So you need a new column, say to the right of the imported times, and use this formula to convert these text cells to times :
=IF(LEFT(A1,1)=":",TIMEVALUE("0"&A1),A1)

and copy down as required.

You should be able to do summing on the new column.


Cheers, Glenn.

 

[haru2002]

Thanks,
That is what I'm looking for. It would have been nice to convert the values within the same cell, but it is an easy work around.