Identifying Peak Demand and Usage with Start/End Time - Hospital

[brookstr]

Here is my dilemna: I am trying to calculate the peak demand/usage times with only a start date/time and and end date/time in Excel. Our hospital uses "yellow alert" when we feel patients in the ER may be unsafe due to extreme circumstances. For each yellow alert instance, I have a start and end time. I am trying to put together a table with Sunday - Saturday across the top and each of the 24 hours on the side. I am then trying to fill in the table by using the yellow alert times.

For example: A yellow alert begins Monday night at 10:30pm and ends Tuesday morning at 9:45am. I want the table to have a .5 for 10:00-11:00pm Monday, a 1 for 11pm-12mdnt Monday, a 1 for 12mdnt - 1:00am Tuesday, all the way to .75 for 9:00am - 10:00am Tuesday.

This will allow us to find times when we may need extra staffing on hand. I can't seem to find a way to fill in the table other than by hand, which is unacceptable. All help is appreciated!

--Ryan

[Randy]

1st welcome

OK next, you're serious about this, right?

I'm a former law enforcement officer who was an EMT, did ambulance duty and spent many, many hours in emergency rooms....That said, even though I was not full time hospital staff I could tell when the peak loads and extra staffing was needed without some chart and research stuff...it came from being there and experiencing it!

Has anybody bothered asking the staff and looking at existing records? For instance, not knowing your location and community and having to rely on personal experience I'd say a couple of peak load times could be from about 9PM Friday to 5AM Saturday and the same timeframe from Saturday to Sunday, also Sunday to Monday from about 11PM-2AM. If you'd look at records you'd probably also find a heavy use during the evening of holidays like Thanksgiving, Christmas and New Years and believe or not probably Full Moon time periods regardless of day of the week.

Just get your hands on you records (sign in sheets primarily) and graph out the data you have, establish a benchmark or key indicator and go for it.

[brookstr]

Randy -- Thanks for the welcome! Most of us know when the peak demand hours are, but it is always important to have supporting data especially when trying to convince those in mahogany hollow that we need extra staffing. There has to be a way out there to perform this.

--Ryan

[Randy]

OK, gotcha and understand your delima.

Really you're just looking at a numbers crunching exercise and probably have the data you need, it's just the task of putting it into a meaningful format.

When you do get your data together you need to add the money numbers in as well...and refer to everything as investment, not cost. One of your ROI's will be potential deferred losses stemming from future litigation when someone dies or their condition becomes more serious due to low staffing numbers.

Also decreased levels of staffing can increase potential health and safety issues with your staff due to stress and other related issues adding to the loss column.

Then there is the potential turnover of personnel who are able to escape into lower stress environments like the cancer and cardiac care wards and the need to train new personnel in ER/trauma care (not an easy task)...this can create additional potential loss both from diminished productivity due to unfamiliarity/ lack of competence and EO (errors and ommisions) from the same causes.

This is just a simple "business" exercise, so approach it as that.