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

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

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:

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

Suzanne

1. Nicky says:

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

2. tom says:

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

3. Ray Avila says:

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

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

4. d'lin says:

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

5. Dave P says:

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

6. Julianne says:

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

7. CRinWP says:

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

8. mitesh parmar says:

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)

9. Diane says:

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?

10. jean says:

How about a formula that eliminates weekends and holidays?

11. bipin says:

How to calculate the duration of time I have worked if end time is not enterned in field?

4:00 PM ????

12. Derek Whitton says:

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