adding a zero in front of a colon
 

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

adding a zero in front of a colon
 

Format the cell with ...
m:ss
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"303mike" wrote:

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

adding a zero in front of a colon
 

Unfortunately, I did already try that. No go.
Thanks for the quick response!

"Gary Brown" wrote:

Format the cell with ...
m:ss
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"303mike" wrote:

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

adding a zero in front of a colon
 

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

"303mike" skrev i meddelelsen
...
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

adding a zero in front of a colon
 

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.

"Per Jessen" wrote:

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

"303mike" skrev i meddelelsen
...
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

adding a zero in front of a colon
 

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

//Per

"303mike" skrev i meddelelsen
...
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.

"Per Jessen" wrote:

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

"303mike" skrev i meddelelsen
...
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