Formula Watch: Calculating elapsed time in Excel

Formula Watch: Calculating elapsed time in Excel

  • Comments 12
  • Likes

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
  • 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

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

  • Hi Nicky, Format the cell in which you are doing the summation to :

    Format cell---> Custom--->[h]:mm:ss

  • 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

  • Add the D (days) in your formula

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

  • 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.....

  • 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

  • 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)

  • 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?

  • How about a formula that eliminates weekends and holidays?

  • 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 ????

  • 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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment