MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find the Elapsed Time Between Two Time Entries

October 22, 2001 - by Bill Jelen

Felipe writes:

I need to find the elapsed time between two moments. Each moment has a date and a time. I need to know how much time was elapsed, in minutes or in hours and minutes.

If the times are in cells A1 & B1 and you want the elapsed time in C1, follow these steps:

  • In cell C1, set up a custom number format of [h]:mm
  • Enter this formula in C1: =MAX(A1:B1)-MIN(A1:B1)

The square brackets around the h will insure that times over 24 hours are still reported as hours instead of days.

Also, the result of a date/time calculation can never be negative. I've used the Max and Min to make sure the earlier time is always subtracted from the later time. You could also use =ABS(B1-A1) or simply make sure that B1 will always be greater than A1.

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.