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

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?