How to Calculate Length of Stay in Excel


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:

Excel calculate length of stay

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

Featured Posts

One Reply to “How to Calculate Length of Stay in Excel”

  1. Thank you! I am being asked to calculate the average length of stay by month. This requires me to calculate the number of inpatient days in a given month and then divide that by the number of discharges in that month. While your formula gets me the average length of stay overall, it does not tell me what the average length of stay was for each month. Is there a way for excel to tell me that in Jan there were x number of inpatient days and y number of patients were discharged?

Leave a Reply

Your email address will not be published. Required fields are marked *