Formula Watch: Calculating elapsed time in Excel

If you use Microsoft Excel to log times, there's a simple technique you can use to quickly calculate the difference between two times. For instance, you might log the start and end times for meetings, or keep track of the entry and exit times for visitors to your office space.

One simple formula can save you from reaching for the calculator or trying to figure out all of those time differences in your head.

In this example, I've entered Start Time in Column A and End Time in Column B.

Elapsed time example

Simply log or convert your dates and times using one of the two Date formats that include times. I used the one highlighted below:

Format Cells, Date

Now simply enter the formula in your Elapsed Time column:

=TEXT(B2-A2, "h:mm:ss")

You can leave off the ":ss" if you don't log seconds, but it's there if you need that level of precision.

Now simply use Fill Down to quickly calculate the elapsed time for any additional rows in your time log.

If your dates and times are already logged in separate columns, simply add them using "+" before using your elapsed time formula. For instance, I've used =A2+B2 with the same cell formatting from above to combine Start Date and Start Time:

Combining Date and Time

Do the same for End Date and End Time, and you're all ready to generate the time differences.

Suzanne