Calculate time

How do I calculate the total time as depicted below?

calc total time
Tom KnowltonWeb developerAsked:
Who is Participating?
TaztugConnect With a Mentor Commented:
Column E should simply be set to the following "=d2-c2"  Ensure the format is shown as a time and not a numeric.  Then at the bottom you can use the sum function for all of column E.
FunkmotorConnect With a Mentor Commented:
I can't see the row numbers, but using the line that says "Rally Training" and assuming that's row 3, you'd use this formula:


The trick is in changing the formatting on the cell to be what you want.  If you set that to "h:mm", you'll get hours and minutes.  (Right-click the cell or group of cells, pick Format Cells, then on the Number tab under 'Custom' you'll see the time choices suited to this.)

Hope that helps.
pmitllcConnect With a Mentor Commented:
I'm assuming you are only counting for full hours.  You can use this formula in E2:



Funkmotor's method seems much easier and a better way to go.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tom KnowltonWeb developerAuthor Commented:
It looks like it is working...

But from 12:00 to 1:00 is not working due to 12 being less than 1.

How do I tell Excel those are TIME values, exactly?

too big
Tom KnowltonWeb developerAuthor Commented:
Nevermind...I think I got it now.
Tom KnowltonWeb developerAuthor Commented:
Thanks everyone.
Ah.  You'll need to set the C and D cells to a Time format that includes AM/PM - unless you type things in using 24-hour time (1:15 is 13:15 sort of thing.)

Just select your C and D fields and set their format using a 'Time' instead of Custom, and there should be one in there you like.

Sorry I missed that.
Tom KnowltonWeb developerAuthor Commented: exactly what I did....

Nope, you were right on target.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.