| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding a colon to military time | Excel Worksheet Functions | |||
| Combine multiple cells (3) adding a Colon (:) | Excel Discussion (Misc queries) | |||
| Adding a zero to the front of numbers | Excel Worksheet Functions | |||
| how to change colon to semi-colon in CP/List Seprator | Excel Discussion (Misc queries) | |||
| Adding + in front of a number | Excel Discussion (Misc queries) | |||
Linear Mode
