Freigeben über

Formula Watch: Calculating elapsed time in Excel

  • Artikel

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

Comments

  • Anonymous
    March 13, 2011
    Hi I have a column in spreadsheet recording elapsed time , but when I add the columns values, I get some strange results! Date From To Number of hours 09-Mar 14:00 16:30 02:30 10-Mar 09:00 12:30 03:30 10-Mar 14:00 18:00 04:00 11-Mar 09:00 12:30 03:30 11-Mar 14:00 18:00 04:00 12-Mar 09:00 12:30 03:30 12-Mar 14:00 18:00 04:00 13-Mar 11:00 12:00 01:00 Total 1.08 I've tried all ways, and can't get it to come right - the total should be 26. thanks if anyone can help

  • Anonymous
    October 19, 2011
    Oops.  I made a mistake on my transcription of your data (4:30 instead of 4:00).  Now I got 26 hours!

  • Anonymous
    December 04, 2011
    Hi Nicky, Format the cell in which you are doing the summation to : Format cell---> Custom--->[h]:mm:ss

  • Anonymous
    January 13, 2012
    Just another example of Excel being (too) helpful. The answer appears to have been converted (reformated) into days (default?) 1.08*24=25.92.  If you had more significant digits you would probably get your 26.0 hours

  • Anonymous
    February 24, 2012
    Add the D (days) in your formula =TEXT(B2-A2, "d:h:mm")

  • Anonymous
    January 15, 2013
    What would the formula be to add the "c" column, or add up all the elapsed times I calculated using the above formula?  I did use seconds but I want a "grand total" of all the time.....

  • Anonymous
    February 20, 2013
    Thanks for this. I tweak it slightly in that I created "Start Time" and "End Time" column heading, in which I type the military time format such as: 13:30, 14:30, 15:30, etc. Then in the formula, I deleted the ":ss" variable and added an "h" to the hour variable, making the formula look like this: =TEXT (B2-A2, "hh:mm") It then produces the number of hours and minutes I want to bill my customers for. So my table basically looks like this: DATE       START TIME   END TIME   JOB DESCRIPTION    CUSTOMER      HOURS 20130220    13:30           15:45            F-SD Wheel Seals     J. Smith               02:15

  • Anonymous
    August 02, 2013
    DATE       START TIME   END TIME    lunch time JOB DESCRIPTION    CUSTOMER      HOURS 20130220    13:30           15:45           0:30              F-SD Wheel Seals     J. Smith               1:45 now how to calculate total hrs of working ex(1:45)

  • Anonymous
    August 14, 2013
    I am downloading data where the time comes into the spreadsheet as 520 for 5:20.  Is there a easy way to convert it to 5:20 so I can calculate elapsed time?

  • Anonymous
    September 17, 2013
    How about a formula that eliminates weekends and holidays?

  • Anonymous
    September 18, 2013
    How to calculate the duration of time I have worked if end time is not enterned in field? Login At Logout At Duration 4:00 PM ????

  • Anonymous
    November 01, 2013
    I have a column of times as shown below which represents the hours of night. These were calculated from a column of daylight hours and subtracting from 24. Cells are formatted as hh:mm. When I add the column the sum is 17032.01 when it should be around 450. The sum cell is formatted as (h):mm:ss. 14:48 14:52 14:56 15:00 15:04 15:08 15:13 15:17 15:21 15:25 15:29 15:33 15:37 15:40 15:44 15:48 15:51 15:55 15:59 16:02 16:06 16:09 16:12 16:16 16:19 16:22 16:25 16:27 16:30 16:33 17032:01