[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
marafaka
Guest

 Posted: Sun Nov 16, 2003 11:31 am    Post subject: Date difference in minutes I can't find an easy way to calculate difference between two dates in hours, minutes or seconds. Should I write my own function somehow or what? Thanks.
JohnV

Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA

 Posted: Sun Nov 16, 2003 12:43 pm    Post subject: Try this. If you format your date cells A1, the start date, and A2, the end date, with one of the date formats and then format the difference cell, A3, as [HH] or [MM] or [SS] and put the formula =A2-A1 in it, then you should get the result in hours, minutes or seconds . The square brackets do the magic. (I hope this works for you because this is my 500th post and I have been designated as a "Super User". I get something right on occasion and I sure would like this is one of them!)
marafaka
Guest

 Posted: Sun Nov 16, 2003 1:27 pm    Post subject: Thanks JohnV, exactly what I was looking for.
dfrench
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

 Posted: Mon Nov 17, 2003 12:57 am    Post subject: I may be being a little too pedantic, as John provided exactly what was required, but the difference formatted in the way described will only show the fraction of a day ie will provide a time of less that 24hrs for "HH" or "HH:MM:SS" regardless of the number of days between start and end. With simple dates (no time) in a1 & a2 , a3 will always show as zero although it actually stores an integer. Failing the existence of a differnce function (and there isn't one for hours minutes orseconds) any arithmetic can be performed on dates and times with the knowledge that they are stored as decimal parts of a day so multiplying the difference by 24 gets the answer in hours and will display correctly if formatted as a number
JohnV

Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA

Posted: Mon Nov 17, 2003 8:30 pm    Post subject:

Hi David,

I'm not sure whether I agree or disagree with what you've said so I'll let you be the judge. I agree that if cell A3 is formated “HH:MM:SS” that the difference between two simple dates will show as “00:00:00” but the appropriate value you speak of will be stored in A3. However, if A3 is formated “[HH]:MM:SS” then the difference will show hours, like “24:00;00” while the same appropriate value will be stored. The following is what you get using the format where HH is between square brackets:
 Quote: 01/01/03 ~~~~~ 01/01/2003 01:00:00 01/03/03 ~~~~~ 01/03/2003 02:05:00 48:00:00 ~~~~~ 49:05:00 ------------------------------------------------------------------ C5=A3+C3======> 97:05:00

With either format there is a big difference between what you see and what is actually stored so one needs to be careful how that value is handled in future formulas based on it. Obviously you are aware of this and the point is made for the benefit of other who might read this.

Now I'm still at a loss to know whether I disagreeded or not. I tend to read your message two different ways.
dfrench
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

 Posted: Tue Nov 18, 2003 2:03 am    Post subject: You are perfectly right, I totally missed the magic of the square brackets. The documentation is pretty sparse too. Always learning with this forum but I think I will give that format a miss. In my defence, I spend a lot of time with the calculation side of spreadsheets and try to keep it so that what I see is what I have got. When the next user comes along and starts calculating with that "number of hours" ... ugh!
JohnV

Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA

 Posted: Tue Nov 18, 2003 11:40 am    Post subject: I thought you could have missed the square bracket part but wasn't sure. Last night in the wee hours I went searching in the macros section for code to get the value from a Form text box. Not to my surprise, I found yours and was able to convert it to my needs. Thanks, I'm still a lost ball in the high weeds when it comes to the API!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum