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.

  • 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:

Scroll to Top