Bed-Nights Happen At Midnight

From time to time, people ask me about how a report handles bed-nights. Unless otherwise specified, a bed-night is counted if a client is booked in at exactly midnight.

That means that there is no minimum stay length. If a client books in at 11:59pm and then books out at 12:01am (2 minutes later), that counts as a bed-night.

That means that if someone books in at 1am and stays the entire rest of the night, that does not count as a bed-night.

That means if someone is there at check-in and sleeps for a few hours then there's an incident and they have to go to the hospital at 11:30pm, that does not count as a bed-night.

Why Midnight?

There's a really simple reason why this is the case. The most simple formula that can be used for report writing is called datediff, short for "date difference" or the difference between two dates. When you want to know a duration of something, you calculate the datediff. You can actually think about it like subtraction. If someone books in on August 1 and books out on August 5, five (August 5) minus one (August 1) is 4. That's a datediff of 4, or four days, or four bed-nights: the night of August 1, the night of August 2, the night of August 3, and the night of August 4.

(The datediff formula - instead of simple subtraction - is there to handle the trickier parts, like how many days are between February 17 2020 and September 4 2021 given that there may or may not have been leap years.)

We're literally counting the number of calendar days, which means that it matters when the calendar ticks over, which happens at midnight. If someone books in on September 20 and books out on September 20, 20 minus 20 is 0, or zero bed-nights.

Consistency is Key

This formula is really clear and simple, so this is the default position for reports that ask about bed-nights. We use it. In our Crystal Reports training, it's what we teach our students to use. And it's so clearly the most simple answer that we would guess that about 90% of independent other report writers would come to the same solution on their own, without reading this article first.

There's a huge advantage to be had if all reports that look at bed-nights are calculating it in the same way. It means that you could run an official HIFIS Report and then run one of our reports and be able to cross-reference the numbers, and they should match. And then you could also write your own report and find that the numbers match, once again. If everyone's using the same calculations, you can be more confident in the data when all the numbers come out the same.

Alternative Approaches

All of that aside, there are possible other ways to calculate bed-nights. We have had more than one client say that the definition of bed-nights they use locally is different, so could we make a special report that does this.

One option is to look at the number of 24-hour periods that a client has stayed for. Basically, instead of counting the number of days between the book-in and the book-out, instead we count the number of hours and then divide by 24. This gets you roughly the same number, but it doesn't specifically matter if they booked in before or after midnight. However, if they only stay for, say, 8 hours, that would count as 0.

Some shelters book people in every evening and out every morning. For a shelter with that pattern, we could simply count the number of stays that are at least a certain length of time, like 6 hours. But that really only works if you don't have any multi-day stays.

A common request is for communities to ask to use a different time instead of midnight. 3am or 4am are pretty common. However, these are a lot more challenging to implement because you can no longer rely on the datediff formula to do its job properly. One thing we could do sneakily modify in the background the times of the book-ins. So for example, if someone booked in at 3:59am and we want to use 4:00am as our cut-off, we subtract 4 hours - 3:59am becomes 11:59pm the previous day - and then we can use our datediff formula. Or, a more roundabout way to do it is to generate a list of dates (i.e. September 19th at 4:00am) and then check for every stay, did it overlap with that timestamp? The latter is a more cumbersome query though that would take longer to execute.

Another thing we've seen is adding a requirement of a minimum stay duration, such as 6 hours. Even if they were booked in at midnight, if the total stay duration was less than 6 hours, it counts as 0.

Unfortunately, all of these alternative approaches is imperfect. Some might solve the problem of what if a client books in at 12:01am, but they can cause other problems too. Maybe the formula becomes so complicated that it's troublesome to troubleshoot. Or maybe you introduce a new cutoff time, but find that an equal number of clients are being missed because they're booking out too early. Or maybe you have one that works great for you, but the rest of the world doesn't see it that way, and now you have multiple bed-night reports that don't match each other.

We've Seen Things

We know that the midnight cutoff is not perfect, but in the grand scheme of things, we would suggest not to stress out over it too much. Why not? There are far, far worse data problems out there! Even if we're just limiting this discussion to shelter bed-nights, there are plenty of shelters who aren't entering data in a timely fashion, and one of the problems with that is if you're entering in data too far after the fact, it's more prone to error.

Some shelters hire a data entry person who comes in once a month to enter all the data into HIFIS, and they don't have any firsthand knowledge of which clients were in what beds at what time, other than what someone wrote down on a piece of paper. Or, they do data entry during normal business hours, Monday to Friday, but don't do any on the weekend. And this sort of problem can spill over to other shelters too: if you prohibit concurrent shelter stays, then a staff at Shelter B cannot book in a client who was staying at Shelter A last night, until the Shelter A staff book the client out first. So Shelter B's numbers might be wonky because of Shelter A's data entry.

Some shelters have policies or cultures that dissuade clients from consenting to data collection and sharing, and so as a result, a large portion of their clients are anonymous. This makes it impossible to identify whether these clients are also accessing services at other shelters, or whether they're restricted from them, or whether they're chronically homeless, or whether they stayed last month under a different alias.

Some shelters have policies where they keep a client booked in to a bed, even when they're not occupying it, as a way to reserve the bed for when the client comes back. For example, we've seen at least one that gives their clients "weekend passes." So they're accumulating shelter bed-nights while not actually even being in the shelter building at all!

If we're not limiting this discussion to shelter bed-nights, there are way worse gaps in knowledge about our homeless-serving system than an occasional bed-night that wasn't counted properly. Clients who are unsheltered or experiencing hidden homelessness have far less data recorded about them than those in shelters. As a general rule of thumb, data for residential programs is usually far, far better than data about non-residential programs. Here are some areas that probably really do need better quality data: For your drop-in centre clients, are they housed or homeless? For your street outreach clients, how long have they been on the streets and where were they before that?

The World According to ACRE

In the grand scheme of things, getting too deep into the weeds about a midnight cutoff versus 24-hour periods or a 4am cutoff or whether the minimum duration should be 4 or 6 or 8 hours is probably not the best use of your time. Finding the perfect definition would probably only improve your data accuracy by 1%-2%. We'd suggest focusing on something that could make a much greater impact on your data quality, such as getting higher completion rates for your mandatory data elements.

Our official recommendation is to go with the midnight cutoff, unless you have a really compelling reason why not, and you know exactly what you're doing. If you choose to diverge from the path well travelled, you should know the risks, and proceed with conscious intent. We wish you well!