How to Create a Length of Stay (LOS) Report in Excel Using Formulas

Have you ever been asked to create a Length of Stay Report and you aren't sure what formulas to use or even how to use formulas, so instead you manually calculate every number and enter it instead of using a formula to calculate the numbers. This is a sample report, complete with formulas that will assist you in creating a quick easy report that can be used over and over. Personally I use this and I have a page set up for each month in my workbook and I copy and paste the primary information from a system generated report directly into this workbook and it does the rest of the work for me.This report will provide the following:

This report will provide the following:

Patient Name

Patient Number

Admission Date

Discharge Date

Estimated LOS

Shortened LOS

Extended LOS

LOS by Discharge Status

1. Copy and paste or manually enter patients first and last name and ID.

2. Copy and paste or manually enter the Admission Date, Discharge Date, and # of Anticipated days (this is the predetermined number of days the
patients planned stay will be; if this is not something you use, then you will not have an Estimated, Shortened or Extended LOS.

3. In Column L you will have to type the name of the Discharge type if you would like this to show. In order to get this to show as an abbreviation (Column J)
I created a separate sheet within the workbook called "Types_of_Discharges" and entered the exact name and then an abbreviation in the next
column. You will note that in Column L there is a formula that will look at the "Types_of_Discharges" sheet and then look at Column L and pull back the
information I have requested based upon the lookup.

5. The # of days planned LOS thru the # of days extended will calculate based upon formulas

6. Once the report is complete you will need to go to FILE>PAGE SETUP> click on Header & Footers change the date to the appropriate month

7. Once the date has been changed you will need highlight from the furthest right column and furthest down row and highlight then go to
FILE>PRINT AREA>SET PRINT AREA

8. The report should be ready to go.

9. The following are the formulas in each column in the event you should ever accidentally erase one:

a) # of days Planned LOS:"=DATEDIF(A2,K2,"d")" this formula would be used in Row 2 Column F you would manually type this into that space but to not
use the " " before and after the formula, I put them there to allow the formula to be viewed.

b) # of days Actual LOS:"=DATEDIF(A2,B2,"d")" this formula would be used in Row 2 Column G you would manually type this into that space but to not use
the " " before and after the formula, I put them there to allow the formula to be viewed.

c) # of days Shortened:"=IF(F2this formula would be used in Row 2 Column H you would manually type this into that space but to not use
the " " before and after the formula, I put them there to allow the formula to be viewed.

d) # of days Extened:"=IF(F2>G2,0",G2-F2)" this formula would be used in Row 2 Column H you would manually type this into that space but to not use the
" " before and after the formula, I put them there to allow the formula to be viewed.

e) Additional Information: "=VLOOKUP(L2,Types_OF_Discharges!$A$1:$B$5,2,FALSE)" this formula would be used to lookup the type of discharge in
Column J. You would manually type this into that space but to not use the " " before and after the formula, I put them there to allow the formula to be
viewed. This formula can be used by utilizing the Function key in your Excel top bar.