Add characters to the start of cell, if cell contains a certain number of characters

[Chookz]

I have encountered a problem with my data set. My export data lists 24 minutes 30 seconds as 24:30, however the computer reads this as 24 hours thirty minutess (as 1 hour 24 minutes 30 seconds is displayed as 1:24:30).
Can i enter a formula/formulas to say:
If cell is 4 characters, add 0: to the start of the cell
If cell is 3 characters, add 0:0 to the start of the cell
If cell is 2 characters, add 0:00: to the start of the cell, and
If cell is 1 character, add 0:00:0
So that 24:30 would read 0:24:30, 5:36 would read 0:05:36 and 20 would read 0:00:20?

 

[Andrew Poulsom]

How is 30 seconds exported?

 

[Chookz]

How is 30 seconds exported?

Hi Andrew,
Im not sure as im home now and the file is on my computer at work. So far ive only had exports of hours (eg. 02:22:15) and minutes (eg. 5:36). But there is the potential that data may be exported in seconds. It doesnt make sense that 30 seconds would export as 30, so im assuming it would export as 0:30.
Any help you can give me would be greatly appreciated

 

[Andrew Poulsom]

Try this formula:

=IF(SECOND(A1),A1,A1/60)

 

[Chookz]

Sorry for the late reply, but thanks very much Andrew, that worked a treat!!