Formatting Times

[ChrisBat]

Hi,

I have received a large dataset that has three columns
with what are supposed to be times; however, as they were
extracted from a legacy system, the format of the time
is '1000 for 10:00. Is there a way that I format it to
show properly? i need to run calculations based on these
three times, and can't do it with the formatting the way
it is now.
Any suggestions??
Thanks,
Chris

 

[Bob Phillips]

Not to format, but you can get it in a separate column with this formula

=TIME(INT(A1/100),A1-INT(A1/100)*100,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
one way (if you always have 4 digit values): Enter the
following in B1 (assumption: col. A stroes your values)
=TIME(--LEFT(A1,2),--RIGHT(A1,2),0)
Format this cell as 'Time' and copy this down for all rows

 

[Guest]

Thanks a lot - you just saved me a bazillion hours of work!
Chris