Forum Discussion

CHileman's avatar
CHileman
Copper Contributor
Jun 07, 2022

Calculate Bed Nights

Hello,

 

I have a table set up where I have an intake date and a discharge date as specific columns. When the patient has not yet discharged, instead of a discharge date the value will be "HERE" (I can change this if needed - it's just been a helpful placeholder). We report a total number of bed nights for two ranges in a month, either the 1st to the 15th, or the 15th to the end of the month. 

 

To do this I added two columns, one to return a "Bed Night Adjusted Intake" and one for a "Bed Night Adjusted DC" - the idea being that if the intake date fell before the reporting period, that the adjusted date would default to the start of the reporting range, and if the discharge fell before the end of the reporting period, that would reflect the date of discharge instead of the end of the reporting period. 

 

These are the formulas I came up with:

Adjusted Intake:

=IF(([@[Bed Night Adjusted DC]]=""),"",(IF([@[Intake Date]]<$A$107,$A$107,[@[Intake Date]])))

Adjusted DC:

=IF([@[DC Date]]="HERE",$C$107,(IF([@[DC Date]]>=$C$107,[@[DC Date]],"")))

 

A107 is the start of the reporting period

C107 is the day after the end of the reporting period (I figured it's easier to just put that instead of +1 to everything when I calculate)

 

I'm missing a way to first check to see if the range between my intake date and discharge date overlap the range between the start of the reporting period and end of the reporting period. I think if I got the formula to first check for that condition, I would stop ending up with negative values for patients who admit after the end date of my reporting range. I do not remove discharged patients from my table. 

 

Any help with this would be greatly appreciated!

 

TIA

 

I've probably dug myself into a corner thinking about it this way and am missing a more elegant solution. 

Formulas and Functions

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 07, 2022

    CHileman Perhaps you can work with the solution used in the attached file. It works with MAX and MIN to allow for start and end dates carrying over month-ends. and I've used three named ranges (start, middle and end) so that the formulae are easier to maintain. And I chose to not use "HERE". In stead I left the DateOut blank.

     

    • CHileman's avatar
      CHileman
      Copper Contributor
      Jun 08, 2022

      Riny_van_Eekelen Thank you so much! This is definitely a different way to approach this - I'll have to dig a little deeper into MAX and MIN functions as they are new to me. But within this system I still encounter the problem of patients who admit after the reporting period as showing negative numbers. But I suppose I can put in another IF function ahead of the formula you use to test whether or not the DATEIN > End of Month, or 15th, depending on which period I'm getting results for. But I think that should work. Thank you again!!!!