Try a formula like

=TIME(INT(A1)/100,MOD(A1,100),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"M Jones" <M wrote in message
...
I have a LARGE (22,000+ rows) excel worksheet with results from a query I
ran
to show admit and discharge times. However the times are not in the right
format to perform calculations so I can measure the length of stay. For
example the time 2:15 a.m. is 215....because my query is pulling data from
our mainframe there is little I can do about how the data comes in...I
have
already looked to see if I can convert the data or compute the data, but
have
been unsuccessful. I have also tried to format the cells so it will show
time as 02:15, but this is not working either. What is working for me is
this....the function TIME. I can put in the function as =TIME(2,15,0) and
it
will display the time 2:15 thus enabling me to perform the correct
calculations. But what is the best way, if any, for me to have the entire
spreadsheet done without having to go into each individual cell (which is
not
feasible) ? Any suggestions?