Change to show time

[Guest]

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?

 

[Bob Phillips]

Try a formula like

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

--

HTH

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

 

[Guest]

The question is how is 2:15 pm displayed, if it is 1415 that you can use a
help column like

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

where A1 would hold the time, you can basically copy down the formula 22000
rows and paste special as values over the old values and remove the help
column

If the value should go beyond 24 hours like 3215 for 32:15 then you wopuld
need something like

=INT(A3/100)/24+MOD(A3,100)/1440

which will work for lesser times as well

Format as [hh]:mm

Regards,

Peo Sjoblom