How do I calculate the difference between two times?

I am so embarrassed by the fact that I can't figure this out.
Cell B2- 8:00 am
Cell C2- 10:50 am
Cell D2- (How do I get this cell to calculate the difference and say 2:50?)

I know this is probably one of the most basic operations, but for the life of me I can't figure it out. Cells B2 & C2 are formatted for 24 hour clock. But if I tell the system to just subtract the two, I get "0.118". Everything I find on the forum search goes beyond what I need. Can anyone help me?

Thank you.

Mac Book, Mac OS X (10.5.4)

Posted on Jul 27, 2008 6:40 AM

37 replies

Sterkur

Level 1

58 points

Sep 4, 2008 6:37 AM in response to KOENIG Yvan

-

OK, works like a charm. Thank you. : )
Any change of doing the total value of column (D) in one cell (D9)?

Instead of going first via cells (F2-F7) and calculate raw total in (F9)
Then calculate the remainder and putting it into time in (G9)
Then putting it all together in one cell (D9)

Calculations used in the picture: (note the use of semicolon instead of comma's in formulas)

(D2-D7) =TIME(0;0;ROUND((TIMEVALUE(C2)-TIMEVALUE(B2)) 24*6060;0))

(F2-F7) =C2-B2

(F9) =SUM(F2:F7)

(G9) =TIME(0;0;ROUND(MOD(F9;1) 24*6060;0))

(D9) =IF(F9<1;G9;INT(F9)*24+HOUR(G9)&":"&RIGHT("00"&MINUTE(G9);2))

Thanks again, great help!

KOENIG Yvan

Level 8

41,845 points

Sep 8, 2008 12:13 AM in response to Sterkur

I was not at home so I just discover your question.

• If values in column Total are time ones, we aren't allowed to add them. So an auxiliary column storing the durations in a decimal format is required.

Given your table,
the simpler scheme would be (English format. You will have to replace commas by semi-colons):
User uploaded file

column D:
=IF(OR(ISBLANK(B),ISBLANK(C)),"",(TIMEVALUE(C)-TIMEVALUE(B)))

cell D9:
=SUM(D2:D7)

column E:
=IF(D="","",TIME(0,0,ROUND(D 24*6060,0)))

cell E9:
=IF(D9<1,TIME(0,0,ROUND(MOD(D9,1) 24*60*60,0)),INT(D9)*24+HOUR(TIME(0,0,ROUND(MOD(D9,1)*24*60*60,0)))&":"&RIGHT("0 0"&MINUTE(TIME(0,0,ROUND(MOD(D9,1)*24*6060,0))),2))

• If values in column Total may be decimal ones, we may use a single formula to calculate the sum.
I put it in D10
=IF(SUM(D2:D7)<1,TIME(0,0,ROUND(MOD(SUM(D2:D7),1) 24*60*60,0)),INT(SUM(D2:D7))*24+HOUR(TIME(0,0,ROUND(MOD(SUM(D2:D7),1)*24*60*60,0 )))&":"&RIGHT("00"&MINUTE(TIME(0,0,ROUND(MOD(SUM(D2:D7),1)*24*6060,0))),2))

Yvan KOENIG (from FRANCE lundi 8 septembre 2008 09:12:59)

donahuesean Author

Level 1

12 points

Jul 27, 2008 8:03 AM in response to KOENIG Yvan

Thank You. I did try multiple searches, but never found anything this precise. I will try harder next time. Thank you again for your help.

KOENIG Yvan

Level 8

41,845 points

Aug 4, 2008 9:04 AM in response to R C-R

Maybe they must learn how to read.

Yvan KOENIG (from FRANCE lundi 4 août 2008 18:04:33)

Aug 4, 2008 3:14 PM in response to KOENIG Yvan

thanks for the formula, not sure i understand it all, but it works...

Aug 14, 2008 12:02 AM in response to wideEyedPupil

Sorry I just read the other posts to say I should not be talking about this on this thread. Correct, I guess. Oops.

Sterkur

Level 1

58 points

Aug 22, 2008 4:37 PM in response to donahuesean

How would one sum up the total of hours in a column without Numbers dividing with the 24h in a day? ie, getting a result more than 24 hours?

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

How do I calculate the difference between two times?