cond formatting ?

[Guest]

I posted a similar ? but was not able to locate it, forgive me for posting
again.

I have 750 cells running down column F that have what is supposed to be a
time. but someone entered the time in the following format 010754, this is
supposed to be 1:07:54 ( military time for 1:07:54 am). Now I am left with
the task of having to change all 750 of these entries to reflect the correct
format. Is there a way to do this easily vai a conditional format or some
other function?

 

[Guest]

hi,

=time(left(a1,2),mid(a1,3,2),right(a1,2))

hth
regards from Brazil
Marcelo

"Brian" escreveu:

 

[Ran Pelly]

I assume all items are 6 digits.

=timevalue(left(E1,2)&":"&mid(E1,2,2)&":"&right(E1,2))

where E1 is the cell where the time is wirten.

note that you wil need to make sure the format of the cell is Time HH:MM:SS

hope i helped

Ran.