I work for a small medical facility,and I am trying to put together a spreadsheet (for a unit that has overnight and occasional multi-day patients) that will calculate the average number of patients by hour of the day for each day of the week. This is to be calculated for a recent five-week span.

My problem is this: I've done projects before like this, but only for units where patients stay for an hour or so each day. I had no problem with that, but since patients are staying longer than 24 hours, I'm not sure how to create a time function that will allow me to ultimately chart if a patient stayed, say, from 8:24 AM on Tuesday until 9:30 PM on Friday.

If this makes sense to anyone - or if there are any templates out there that you think I might find useful - please let me know!

Thanks in advance for your help!