Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change time format from :0 to 0:0
I have tons of times in this format:
:42:01 :02:58 :04:45 :25:13 and need to change to: 0:42:01 0:02:58 0:04:45 0:25:13 Pls help... tia Donald |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change time format from :0 to 0:0
=TIME(0,MID(A1,2,2),RIGHT(A1,2))
fomat cells as h:mm:ss copy down "Donald B" wrote: I have tons of times in this format: :42:01 :02:58 :04:45 :25:13 and need to change to: 0:42:01 0:02:58 0:04:45 0:25:13 Pls help... tia Donald |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change time format from :0 to 0:0
:42:01
:02:58 :04:45 :25:13 are all Text not times try using this formula in a newly inserted column: =("0:"&(SUBSTITUTE(J1,":","",1)))+0 then copy and paste the column back as Paste Special values and reformat the column as time then delete the original column. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Donald B" wrote in message ... I have tons of times in this format: :42:01 :02:58 :04:45 :25:13 and need to change to: 0:42:01 0:02:58 0:04:45 0:25:13 Pls help... tia Donald |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change time format from :0 to 0:0
Thank you both... both formulas work great!
"Sandy Mann" wrote: :42:01 :02:58 :04:45 :25:13 are all Text not times try using this formula in a newly inserted column: =("0:"&(SUBSTITUTE(J1,":","",1)))+0 then copy and paste the column back as Paste Special values and reformat the column as time then delete the original column. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Donald B" wrote in message ... I have tons of times in this format: :42:01 :02:58 :04:45 :25:13 and need to change to: 0:42:01 0:02:58 0:04:45 0:25:13 Pls help... tia Donald |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change time format from :0 to 0:0
"Donald B" wrote...
I have tons of times in this format: :42:01 :02:58 :04:45 :25:13 and need to change to: 0:42:01 0:02:58 0:04:45 0:25:13 Are these numbers formatted as ":mm:ss"? If so, change the number format to "[h]:mm:ss". If they're text, and you want to leave them text, use ="0"&x where x is the address of a cell containing one of these times. If they're text and you want to convert them to values, use the formula =--("0"&x) and format appropriately. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average rate of change per given time period between 2 moments in time of a value | Excel Worksheet Functions | |||
How can I change time format to decimal without losing hours? | Excel Discussion (Misc queries) | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
change format for time value | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |