How do I calculate the difference between two times?

Perhaps it would be helpful to you as well as other users if you were to post answers to frequently asked questions in the forum titled "User Tips Library".

These posts could include explanations and examples, as well as reminders about issues such as localization differences.

One change to Numbers itself I think might be very useful would be to enable users to save custom formulae in some kind of built-in user library. This could be very helpful for things such as duration calculations, and many more. One can do this rather inelegantly by saving such calculations in user templates, of course, but that is much more cumbersome. I have of course suggested this to Apple through Feedback. Perhaps other users would like to do so as well.

Message was edited by: Eden Sarfaty

The User tip is quite ready but I asked to someone whose main language is English to check it.

Alas, I'm not sure that the "User Tip Libray" is really used by posters which fails to search in the existing Help or PDF User Guide.

Some useful tips are available in the AppleWorks area but the treated questions continue to be asked quite once a week 😟

Yvan KOENIG (from FRANCE vendredi 22 août 2008 19:07:05)

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?

Are you asking seriously or are you joking ?

In this thread, on 21 août 2008 21:58 you posted a screenshot giving the response.

Here it is one more time with a complementary formula.



In column D the duration is calculated (in decimal days) with the quick and dirty formula
=C-B

in D9 a quick and dirty formula calculates the sum of these durations(always in decimal days)

=SUM(D2:D5)

in E9, a quick and dirty formula calculates the part of the total duration fitting in the range 00:00:0 … 23:59:59
=TIME(0,0,ROUND(MOD(D,1) 24*6060,0))

in F9 is one kind of formula displaying the total sum:
=IF(D>=1,INT(D)&" day ","")&E

I don't like it because it is language specific.

in G9 is an other kind of formula displaying the total sum:
=IF(D<1,E,INT(D)*60+LEFT(E,2)&RIGHT(E,LEN(E)-2))
in H9 is the new one used to display the total sum:
=IF(D<1,E,INT(D)*60+HOUR(E)&":" &RIGHT("00"&MINUTE(E),2))

It's my preferred one

It's your task to use the one which fits your needs.

Some users accept perfectly a duration displayed in decimal days as in D9
Others wish to display a more conventional way. This is what are doing the formulas in F9, G9 and H9.

Of course, for those running Numbers inEnglish but using the decimal comma, replace the commas by semi-colons in the given formulas.

Yvan KOENIG (from FRANCE samedi 23 août 2008 15:24:16)

Thanks to pw1840,
here are two corrected formulas.

in G9 is an other kind of formula displaying the total sum:
=IF(D<1,E,INT(D)*24+LEFT(E,2)&RIGHT(E,LEN(E)-2))
in H9 is the new one used to display the total sum:
=IF(D<1,E,INT(D)*24+HOUR(E)&":" &RIGHT("00"&MINUTE(E),2))

I forgotten that most of the time, a day contains 24 hours, not 60 ones.
It seems that I posted from the North pole 😉

Yvan KOENIG (from FRANCE dimanche 24 août 2008 10:45:23)

-

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!

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


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)