Allocate Time Range over each hour of the day?
Greatings from the SF Bay Area. I'm lost on a particular problem.
I have patients that are in and out of different departments like ED and Clinics. I'd like to take an arrival-to-discharge time, and spread the minutes/hours they were 'in-department' during the day. For example, Patient 'A' presents at 14:15 and is discharged at 16:25. I'd like to allocate his time as .75 hours (or 45 monutes) between 14:00 and 14:59, 1 hour between 15:00 and 15:59, and .42 hours (25") between 16:00 and 16:59. I haven't a clue how to efficiently do this. Thanks in advance for help on this... hopefully it will shorten waits in the Emergency Room. I envision a spreadsheet delineating Hour of Day on the top row, and patients down column 'A'. Each patient stay would be noted as hours or minutes in a row over the appropriate hour columns. -Bill |
Allocate Time Range over each hour of the day?
Assuming timein data is in cell b2 and timeout data is in cell c2, i have
prepared a formula for you: =IF(HOUR(B2)=14,IF(HOUR(C2)=14,(MINUTE(C2)-MINUTE(B2))/60,(60-MINUTE(B2))/60),"") this formula calulates time between 14:00 and 14:59 hours, just copy this formula to different time intervals and just change 14 to the hour interval you want say 10, 15...etc also note that the number 14 comes 2 times in the formula so the change also has to be done 2 times -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "Bill Elerding" wrote: Greatings from the SF Bay Area. I'm lost on a particular problem. I have patients that are in and out of different departments like ED and Clinics. I'd like to take an arrival-to-discharge time, and spread the minutes/hours they were 'in-department' during the day. For example, Patient 'A' presents at 14:15 and is discharged at 16:25. I'd like to allocate his time as .75 hours (or 45 monutes) between 14:00 and 14:59, 1 hour between 15:00 and 15:59, and .42 hours (25") between 16:00 and 16:59. I haven't a clue how to efficiently do this. Thanks in advance for help on this... hopefully it will shorten waits in the Emergency Room. I envision a spreadsheet delineating Hour of Day on the top row, and patients down column 'A'. Each patient stay would be noted as hours or minutes in a row over the appropriate hour columns. -Bill |
All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com