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 |
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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 06:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com