adding a zero in front of a colon

3

303mike

Hello,
I am importing data into a worksheet (Excel 2002 SP3) that is minutes and
seconds. If the data is for one minute 27 seconds, it imports as 1:27. If it
is four seconds, it shows :04.
Is there a way to get all the times under a minute to display as 0:04? I've
tried all the existing formats from the tool bar and none of them work.
Thanks!
Mike
 
P

Per Jessen

Hi

Excel is seeing :04 as a text, so we have to convert it into a proper time
value. To do that we use a helper column. Also excel see 1:27 as 1 hour 27
minutes. The formula below will take care of both issues:


=IF(LEFT(A1;1)=":";TIMEVALUE(0 & A1 )/60;A1/60)

Hopes this helps.
....
Per
 
3

303mike

Oops. I just tried entering the formula and received an error. I will try to
play with the formula to get it to work. Any additional suggestions would be
most appreciated.
 
P

Per Jessen

My fault, I forgot to substitute semicolon with comma in the formula, which
you use in most of the world...

//Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

In Excel, how do I display a leading zero? e.g. 05 for 5 ? 1
How can I keep a zero (0) at the front of a set of numbers. 2
Adding a zero to the front of numbers 5
Imported time text to seconds 7
How do I add a zero in front of a text formatt number? 3
space in front of a semi-colon 3
showing an empty cell instead of a zero in a formulated cell. 1
Adding zero in front of a letter 2

Top