How do I calculate the difference between…

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

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

Reply

Aug 9, 2008 2:22 PM in response to KOENIG Yvan

Maybe they must learn how to read.

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

First, let me thank you for your willingness to spend the time to provide specific technical answers. I have found them helpful on this and other occasions.

Second, let me compliment you on your command of English; it is better, alas, than that of many native speakers.

Third, let me say that I learned to read many years ago, and keep in constant practice. Nevertheless, the text you quoted above from the manual was not clear to me. In my humble opinion, more frequent good examples (such as yours) would help the documentation. While I take every opportunity to send feedback to Apple, I often find descriptions of other users' difficulties instructive, and their complaints and suggestions helpful, if only as a warning about what to avoid. As applied to the specific issue here,

Fourth, let me say that for this software to meet Apple's design reputation for ease of use, calculating date and time differences for even the most casual user should be trivial. It is not.

Thank you again for your continuing willingness to be helpful.

Reply

R C-R

Level 6

17,782 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,807 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,782 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 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.

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,807 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

KOENIG Yvan

Level 8

41,807 points

Aug 22, 2008 3:39 AM in response to Sterkur

Your problem is a well known one.
It was described and responded many times.

You are using Numbers in a country for which there is no localised version.

So, it's the English version of Numbers which is used.
Alas, your system is set to use the comma as decimal separator.
So, you must use semi-colon as parameter separator in your formulas.
In column G for instance, YOU must use:
=IF(D<1;E;INT(D)*60+LEFT(E;2)&RIGHT(E;LEN(E)-2))

This doesn't appear in the Help because the Numbers's designers forgot this situation as well as they forgot that the German version is used in Germany with decimal comma and in Swiss with decimal period.
They also forget that the French version is used in France with decimal comma and in Swiss with decimal period.

You may get rid of that entering my iDisk:

<http://idisk.me.com/koenigyvan-Public?view=web>

and downloading:

for iWork:for_Numbers:pour_modifierNumbers.zip

You don't like screenshots made from my French system. You will have to live with them. I will not switch to the english version each time I try to help someone.

I'm not a moderator. I'm just an endUser like you.
And of course, I will not stop to post my well known reminder which doesn't eat much time. It just requires a single copy paste.
After all, taking care of it, you would not be here saying that my formulas aren't working when YOU are the culprit.
There are 8 main localised versions of Numbers. These versions may be used with the comma or the period. I will certainly not give 8*2 = 16 versions of the formulas.
I will continue to give the standard English ones.
You are using an other setting (which is perfectly correct) but it's your duty to adjust the formulas accordingly.

+-+-+-+-+-+-+-+-+

Worried Life Blues 2008

Discussions

+Apple Discussions, launched in August, 2000, have grown rapidly in usage and features. The main features include personalization, subscription capabilities and email capabilities. _For information on how to use Discussions, please visit the Discussions Help Page_. Cookies should be enabled and an Apple ID account is required if you would like to contribute to the discussions.+
…
+*What is Apple Discussions and how can it help me?*+
+
Apple Discussions is a user-to-user support forum where experts and other Apple product users get together to discuss Apple products. … You can participate in discussions about various products and topics, find solutions to help you resolve issues, ask questions, get tips and advice, and more.+
+_If you have a technical question about an Apple product, be sure to check out Apple's support resources first by consulting the application Help menu on your computer and visiting our Support site to view articles and more on our product support pages._+
…
+*I have a question or issue*—+
+how do I search for answers? _
It's possible that your question or issue has already been answered by other members so do a search before posting a question._ On most Apple Discussions pages, you'll find a Search Discussions box in the upper right corner. Enter a search term (or terms) in the field and press Return. Your results will appear as a list of links to posts below the Search Discussions Content pane.+
…
+Search tips are available here:+
+ http://discussions.apple.com/help/search-tips.html+

+-+-+-+-+-+-+-+-+

Yvan KOENIG (from FRANCE vendredi 22 août 2008 12:32:13)

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,782 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,807 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,807 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

How do I calculate the difference between two times?