Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Change to show time
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? |
#3
|
|||
|
|||
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 "M Jones" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOCK TIME | Excel Discussion (Misc queries) | |||
time differences in a column | Excel Worksheet Functions | |||
Accumulate weekly time to total time in Excel. | Excel Discussion (Misc queries) | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions | |||
Help - Information with time and date | Excel Discussion (Misc queries) |