Length of Stay
 

I need to start monitoring length of stay patient stay in the hospital. I have two worksheets, one contains patient name and admission date and the other contains patients name and discharge date. How can I use these two sheets to give me the length of stay.

-------------------------------------------------------------------------

Dear Lhysell, Good Morning.

Imagine your scenery like this:

Plan1
___A________B_________C__D_____________
1__Patient___Admission______Lenght of Stay(Days)
2__Name A___1/1/2011______=FORMULA
3__Name B___1/2/2011______=FORMULA
4__Name C___1/3/2011______=FORMULA
5__Name D___1/4/2011______=FORMULA
6__Name E___1/5/2011______=FORMULA
7__Name F___1/6/2011______=FORMULA
8__Name G___1/7/2011______=FORMULA
9__Name H___1/8/2011______=FORMULA
10_Name I____1/9/2011______=FORMULA
11_Name J____1/10/2011_____=FORMULA


Plan2
___A________B_________C
1__Patient___Discharge
2__Name A___2/21/2011
3__Name B___2/10/2011
4__Name C___2/15/2011
5__Name D___2/17/2011
6__Name E___2/1/2011
7__Name F___2/3/2011
8__Name G___2/7/2011
9__Name H___2/10/2011
10_Name I____2/20/2011
11_Name J____2/21/2011

Do FORMULA like this:

Plan1 D2 -- =VLOOKUP(Plan1!A2,Plan2!A:B,2,FALSE)-Plan1!B2

Copy this formula down to the other cells at D column

ATTENTION:

The result must be like this:
-----------------------------------------------
Plan1
___A________B_________C__D_____________
1__Patient___Admission______Lenght of Stay(Days)
2__Name A___1/1/2011______51
3__Name B___1/2/2011______39
4__Name C___1/3/2011______43
5__Name D___1/4/2011______44
6__Name E___1/5/2011______27
7__Name F___1/6/2011______28
8__Name G___1/7/2011______31
9__Name H___1/8/2011______33
10_Name I____1/9/2011______42
11_Name J____1/10/2011_____42
-----------------------------------------------


Thry this one and tell me if it worked for you.
Fell free to ask anything about it.

What if patient 1 on plan 1 does not correspond to patient 1 on plan 2. These 2 sheets are populated by daily reports and not manually input.

--------------------------------------------------------------------------

Dear Lhysell, Good Afternoon.

If you are asking this, it´s because you didn´t executed nor tested my suggestion for you.

Please, implement and test my suggestion.

You´ll see that the order of patients don´t matter.

You can improve the formula that I suggested you, to controlling the result when the DISCHARGE DATE is zero(0) (Blank).

Change the formula :
Before : Plan1 D2 -- =VLOOKUP(Plan1!A2,Plan2!A:B,2,FALSE)-Plan1!B2

Now....: Plan1 D2 -- =IF(VLOOKUP(Plan1!A2,Plan2!A:B,2,FALSE)=0,"",VLOOK UP(Plan1!A2,Plan2!A:B,2,FALSE)-Plan1!B2)


Do this and tell me if it worked for you.

I had tried but being new to excel I was not transcribing the formula exactley as you had suggested. The formula works just as it should with the exception of how many times we have repeat patients and for these I am getting negative numbers in the length of stay. I will keep trying to figure this one out. Thank you very much for your assistance.

Larry