Time Format replace

[kris ball]

Hi All,

I have a data dump which I feed into Excel and part of the report is in Time Format 00:00:00. In some of the cell the report shows as :04 I need to build something that will bring this back as 00:00:04.

Can someone please help as I have to go through 3k+ cells to re format.

Kris

 

[Stormseed]

Hi All,

I have a data dump which I feed into Excel and part of the report is in Time Format 00:00:00. In some of the cell the report shows as :04 I need to build something that will bring this back as 00:00:04.

Can someone please help as I have to go through 3k+ cells to re format.

Kris

Format the cell/cells to h:mm:ss.

Select the cell or range of cells --> Excel menubar --> Format --> Cells --> Number tab --> Custom --> in the Type option --> type 'h:mm:ss' without the quotes --> Click on OK.

 

[kris ball]

This is the first thing I did but it does not work I need to insert a formula or Macros to re-write the cell data?

 

[ysrmkp]

hi,

Could u please send the sample file to ysrmkp at gmail dot com, so that i can do something.

Regards
Rajesh

 

[kris ball]

Email has been sent

 

[ysrmkp]

hi,

try using this formula...'

=IF(LEN(D2)=3,TIME(0,0,RIGHT(D2,2)),D2)

in cell E2. it should work.

Rajesh

 

[kris ball]

hi,

try using this formula...'

=IF(LEN(D2)=3,TIME(0,0,RIGHT(D2,2)),D2)

in cell E2. it should work.

Rajesh

Love you
you don't know how much time you've just saved me!

 

[ysrmkp]

happy that it worked...

Rajesh