Calculate time

How do I calculate the total time as depicted below?

calc total time
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FunkmotorCommented:
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:

=D3-C3

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.
0
pmitllcCommented:
I'm assuming you are only counting for full hours.  You can use this formula in E2:

=IF(HOUR(D2)=HOUR(C2),0,IF(MINUTE(D2)>=MINUTE(C2),HOUR(D2)-HOUR(C2),HOUR(D2)-HOUR(C2)-1))

EDIT:

Funkmotor's method seems much easier and a better way to go.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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
0
Tom KnowltonWeb developerAuthor Commented:
Nevermind...I think I got it now.
0
Tom KnowltonWeb developerAuthor Commented:
Thanks everyone.
0
FunkmotorCommented:
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.
0
Tom KnowltonWeb developerAuthor Commented:
...is exactly what I did....


Nope, you were right on target.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.