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

=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
>

> :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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Donald B" <(E-Mail Removed)> wrote in message
news:4C3905A6-2EE7-469A-A702-(E-Mail Removed)...
>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
>
>

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
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Donald B" <(E-Mail Removed)> wrote in message
> news:4C3905A6-2EE7-469A-A702-(E-Mail Removed)...
> >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
> >
> >
>
>
>

"Donald B" <(E-Mail Removed)> 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.