Anyone who records time-related data such as room bookings will eventually be asked to provide some basic stats--for example, how much is a given room in use? This post shows you how to quickly calculate the duration of a time span based on start and end times.

First, be sure that your start and end times in Excel are entered correctly! If you don't include AM or PM when you enter a time of day, Excel assumes that the time is AM. Review your data to ensure that you don't have AMs that should be PMs and vice-versa.

To calculate the duration, subtract the start time from the end time.

Duration = End Time - Start Time

The result will be shown in Excel time format, as seen in the image below. Yours might still include AM in the time formatting--if you don't want to see that, open the Number formatting dialogue and choose a Time format that doesn't display the AM/PM, like we did.

You'll find it easier to summarize values that are in decimal format--e.g., 2.5 instead of 2:30:00. To do this, multiply the duration by 24. If your result still looks like a time, change the Number formatting to General.

Length = Duration * 24

Now you have the length of time as a decimal number that will be easy to add up using a formula, or to summarize in a data table or pivot table!