You can use the following formula to calculate the length of stay in Excel:
=IF(C2=B2, 1, C2-B2)
This particular formula calculates the number of days between the starting date in cell B2 and the ending date in cell C2.
Note: If the starting date and ending date happen to be on the same day, then the formula returns a value of 1 to indicate that the length of stay is 1 day.
The following example shows how to use this formula in practice.
Example: How to Calculate Length of Stay in Excel
Suppose we have the following dataset in Excel that shows the admission date and discharge date for various patients at some hospital:
To calculate the length of stay for the first patient, we can type the following formula into cell D2:
=IF(C2=B2, 1, C2-B2)
We can then click and drag this formula down to each remaining cell in column D:
Column D now shows the length of stay for each patient.
For example:
- Andy stayed for 3 days.
- Bob stayed for 4 days.
- Chad stay for 1 day.
And so on.
Note that patients who were admitted and discharged on the same day have a length of stay of 1 day.
We can then type the following formulas into cells D14 and D15, respectively, to calculate the total length of stay for all patients and the average length of stay per patient:
- D14: =SUM(D2:D13)
- D15: =AVERAGE(D2:D13)
The following screenshot shows how to use these formulas in practice:
From the output we can see:
- The total length of stay for all patients was 152 days.
- The average length of stay per patient was 12.67 days.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Check if Cell Contains Date
Excel: How to Get Date from Week Number
Excel: How to Convert Date to YYYYMMDD Format
