# Calculate time

How do I calculate the total time as depicted below?

LVL 5
###### Who is Participating?

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

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:

=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

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

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

0

Web developerAuthor Commented:
Nevermind...I think I got it now.
0

Web developerAuthor Commented:
Thanks everyone.
0

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

Web developerAuthor Commented:
...is exactly what I did....

Nope, you were right on target.
0
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.