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