How do I calculate the difference between…

Level 6

17,830 points

Aug 10, 2008 9:15 AM in response to Eden Sarfaty

I do not wish to renew the debate about the documentation but I would like to add that Apple has provided in its help system the ability to update help topics: when you open the help window, search in help, or choose some of its links, there is a delay while the help system contacts Apple to see if there are any updates or changes to the help topics. Some people refer to this as a "phone home" feature, & of course it only works if you are connected to the Internet, but I believe the updates are stored locally once received, so constant connectivity is not required.

This means that while a change in the application itself can only be done in an update (or with a hack), changing the help can be done at any time. So, if you have any suggestions for that (like adding more examples or even new help topics devoted to durations) you can use the link provided in most help topics to send those ideas directly to Apple.

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 10, 2008 9:49 AM in response to Eden Sarfaty

I'm planning to write a synthesis upon calculations with time values.

I feel that some of you are too severe upon this area.

Apple made a choice.
It is different from what we are accustomed to but it is not complicated.

The only thing to really understand is that:

_a time value is always a date-time one_ .

When we enter simple time value, we must strip the date part to get correct results.

When we compute a duration, always do it as a numeric item which would be a decimal count of days.

Knowing that, everything becomes simple.

Yvan KOENIG (from FRANCE dimanche 10 août 2008 18:48:53)

Reply

R C-R

Level 6

17,830 points

Aug 10, 2008 11:57 AM in response to KOENIG Yvan

Apple made a choice.
It is different from what we are accustomed to but it is not complicated.

I believe the core issue is that a lot of users think it is more complicated that it needs to be & hope to see it made less complicated somehow.

The tricky part is the "somehow" part. 🙂

Reply

Aug 13, 2008 11:40 PM in response to R C-R

I have arrived at this post because, I too, searched for these answers. The merits or otherwise of Apple's choosen implementation is not what is important here. What is important to note is that I, a moderately experienced spreadsheet user since almost Visicalc days, could not get an easy grasp on how to construct a time sheet from the function prompts, the help nor the User Manual.

I've used Appleworks and Excel to make many timesheets in the past and much more complicated documents also. On Mac and PC in the case of Excel.

Excel in particular has wizards and examples in it's onscreen help and manuals of how to subtract one time from another. The absence of this material, given the tricky formula required when compared to excel, is most definitely a problem Apple could well address.

Thanks as always Yvan KOENIG for your concise answers. Be nice.

Al

Reply

Aug 21, 2008 1:36 AM in response to KOENIG Yvan

Used Yvan's formular:
=IF(OR(ISBLANK(B),ISBLANK(C)),"",TIME(0,0,ROUND((IF(TIMEVALUE(C)>TIMEVALUE(B),0, 1)+TIMEVALUE(C)-TIMEVALUE(B)) 24*6060,0)))

In the top cell of the column I get a number value ie 0:29 (29 mins).

When I fill down or paste into other cells(Menu>Insert>Fill Down), at first I got date values ie. 1/01/04 12:29 AM and 12:29 AM even though the formating is set to number on all the cells. After some more copying and pasting the results change to number values. Vary strange. Any observations appreciated.

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 21, 2008 3:18 AM in response to wideEyedPupil

As I often explained, to work about time, Numbers knows only date-time values.
The formula returns a datetime value whose date part is 01/01/1904 which is the starting point of Apple's date numbering.
To get rid of it in the display, we must say DATE to none and Hour to the wanted format.

If you get a numerical value, I guess that you changed something in the formula because there is no way to display a number when the cells's content is returned by the TIME() function.

Look closely at the formula embedded in the cell displaying a number to find what was changed.

Yvan KOENIG (from FRANCE jeudi 21 août 2008 12:17:53)

Reply

Sterkur

Level 1

58 points

Aug 21, 2008 1:13 PM in response to KOENIG Yvan

KOENIG Yvan wrote:
Question asked and responded several times.

Numbers states clearly in the Help and the PDF Users Guide that it doesn't know a "duration" object but a time one which is restricted to the range 00:00:0 to 23:59:59.

For durations we must fool it.

in column D the formula is:
=C-B
in column E the formula is
=TIME(0,0,ROUND(MOD(D,1) 24*6060,0))
in column F the formula is
=IF(D>=1,INT(D)& " day ","")&E
in column G the formula is
=IF(D<1,E,INT(D)*60+LEFT(E,2)&RIGHT(E,LEN(E)-2))

In column E as well as in column F, if the duration is greater than 24 hours, the result is not a time value but a string so, the value in column D may be useful which is the duration with the day as unit value.
</div>

Thank you for your willingness to contribute to these discussions. I have tried all the formulas in the quote above and get an error all of them. The same applies to the formulas in the beginning of the thread. I get a red triangle syntax error. I don't know if the reason is the way I insert the time, the formatting of the input cells or the formatting of the output cells. It would be helpful to know how each and every cell should be formatted and how the time should be inserted. The way I insert time is 01:00 10:00 18:00 23:59 ie 24h clock.
How would one sum or add up a long column of such calculations to get the total hours and minutes?
Thank you.

Regarding your instructions to people posting, you seem to me like Don Quixote when you are telling people how to use the discussions.
I have to agree with many that the help and the manual for many of the Apple products feels inadequate. For some like you it may not be. For some like me it may be. Different strokes for different folks.
For me I may understand some of the Functions used to put in a formlua. But I may not be aware of the drawbacks of a specific Function. I may also not be aware or have the experience and knowledge to add together different Functions and in which order they need to be to get a workable formula. Some may have good knowledge of scientific calculations, others have a good knowledge of languages, design, human relations or perhaps cooking. Have different background.
That is what this site is for. To help people at different levels to get the most out of the products they are using. If you are an Apple moderator or if you can not live with that and you have to try each and every time to teach people how to search or use the manuals, maybe you are better off not posting to this site. In my opinion that would be a tragic waste of talent and knowledge because you seem extremely knowledgeable and also willing to share that knowledge.
For me as a user, it is extremely frustrating and time consuming having to read through a lot of garbage of how to and how not to post to this site just to find the hidden gems that you truly share with this community.

It would also be more useful if you would have all material on here in the english language as this is a english language site. I have for instance no idea what it say's in column B and C above but I can only guess.

Thank you again for all your helpful posts. May they be many more.

Message was edited by: Sterkur

Reply

Aug 22, 2008 4:58 AM in response to KOENIG Yvan

Thanks Koenig,
(Hope that's your first name)

In this particular case your answer makes an incorrect assumption, but thats probably because I mislead you. When I entered the formula I assumed the result was a number, I was wrong as you indicated it had to be a date-time result. I thought it was a decimal number that Numbers had reformatted to a time value (now I know it wont do that!).

When I highlighted the column and selected Menu>Insert>Fill Down. The cells below all had dates and times. So I assumed that they were date-time values (correct). The formula in no way changed. Same when I copied and pasted the cell one at a time. The other cells may have had different formatting options of Automatic or Date-Time. I haven't been able to replicate the experience exactly but formatting seems to be a part of the problem.

To save you time:
"As I often wrote, when a question is not detailed enough, the response may be inaccurate.
When the question gives all needed details, the response may be accurate 😉"

Thanks for your response it got me on the right track.

Reply

R C-R

Level 6

17,830 points

Aug 22, 2008 5:12 AM in response to Sterkur

I tend to agree with Sterkur that it is neither very helpful nor at all realistic to assume that Apple-provided documentation or existing Discussions posts will be adequate for all users and that posting frequent, generic reminders about using the Discussions search feature is a Quixotic endeavor that in effect degrades the "signal to noise ratio" of this forum -- that is, it is itself & stimulates more of what is basically off-topic opinion, commentary, & advice that users must wade through to get to the useful bits.

But ...

I cannot agree that anyone would be better off if Yvan quit posting to this forum (with or without the generic reminders) or that this is "a english language site." Discussions is open to all Mac users, whatever language they choose to use. Moreover, there are many issues (like yours?) that directly involve language differences & it is unlikely that any single-language version of Discussions would be as good a forum for their discovery, discussion, or resolution. Obviously, it is harder to use examples given in a language one doesn't understand well or at all, but one must also consider that expertise is not confined to the speakers of any one language & that Discussions users form a worldwide community.

Putting it all together, it seems to me the only reasonable approach is to assume there will always be misunderstandings & confusion, both among questioners & answerers, & that to ask for clarification when needed is the only efficient way to deal with this.

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 22, 2008 8:15 AM in response to wideEyedPupil

Hello wideEyedPupil.
No luck, my first name (often called christian name but as an atheist I don't use this formula) is Yvan. KOENIG is my family name.

In this thread, I responded to two beings.
So I will assume that your message is linked to the one addressed to yourself.

I will be glad to know which values are stored in columns B and C to give odd results in the column using my formula:
=IF(OR(ISBLANK(B),ISBLANK(C)),"",TIME(0,0,ROUND((IF(TIMEVALUE(C)>TIMEVALUE(B),0, 1)+TIMEVALUE(C)-TIMEVALUE(B)) 24*6060,0)))

The original question was:

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

So, columns B and C are supposed to store time values of the same days.
If they really do, the formula behaves correctly.

If the columns B and C contains time values which aren't in the same days, the detail was missing and the question required an other response which is the one Sterkur asked upon.

So I continue to think that if the response was inadequate it's because the question was incomplete.

The poster may know if the entry time values are in a same day or if they aren't.
The endUser which try to help can't guess that. We aren't wizards, sooth-sayers or fortune-tellers 😉

Yvan KOENIG (from FRANCE vendredi 22 août 2008 17:15:20)

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 22, 2008 8:17 AM in response to R C-R

You will not be surprised to read:

• Sterkur message will not push me out of the forum
• I will continue to post the Worried Life Blues when I feel useful to post it.

Yvan KOENIG (from FRANCE vendredi 22 août 2008 17:17:00)

Reply

Aug 22, 2008 9:47 AM in response to KOENIG Yvan

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

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 22, 2008 10:07 AM in response to 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)

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 23, 2008 6:24 AM in response to Sterkur

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.

User uploaded file

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)

Reply

KOENIG Yvan

Level 8

41,845 points

Aug 24, 2008 1:45 AM in response to KOENIG Yvan

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)

Reply

How do I calculate the difference between two times?