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