How to determine Length of Stay in excel

[Shaina Na]

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? So any ideas about this then let me know.

[AnitG]

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
-----------------------------------------------
Try this one and i hope it worked for you.

[Shaina Na]

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. Thanks for reply

[AnitG]

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. Thanks for reply

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,"",VLOOKUP(Plan1!A2,Plan2!A:B,2,FALSE)-Plan1!B2)

Do this and tell me if it worked for you.