DateDiff problem

[Carl_R]

I have a textbox 'Duration' whose control source is =DateDiff("n",[DurationFrom],[DurationTo])/60

All data is stored in a table.

This sort of works if the times entered in DurationFrom and DurationTo are nice and round (like 10:00 11:30 which would return 1.5hours)

I get problems when times are entered like:
10:00 11:22 which returns 1.36666666666667.
How would I get this to show real hours and minutes (like 1hour22minutes)?

It also does not seem to like 00:00 or 24:00 (not sure why).



(Access97)

 

[ColinEssex]

I did a search for you here - have a look at this thread-

http://www.access-programmers.co.uk...id=13869&highlight=datediff+hours+and+minutes

or search for "DateDiff Hours and minutes" there's lots of threads to plough through

Col

 

[Carl_R]

Pat/Colin - when I'm all growed up, I wanna be just like you
Cheers

 

[ritchieroo]

Under the bonnet (hood if you're american) the Date data type is fixed-precision, and therefore shouldn't really suffer from floating point errors. However, the DateDiff function returns an Variant (long integer), and there is some dynamic casting when you divide this by 60 that returns the result as a Double.

That's the why over and done with. Getting back to your original problem there is a really simple solution that relies on the the fact that the Date data type is actually a numeric: use maths!

This will give you what you want...

=CDate([DurationTo] - [DurationFrom])

 

[ritchieroo]

I should admit this only works well if your dates are less than 24 hours apart

 

[raskew]

The short answer:

When dealing with Date/Time data type, don't ever use times without dates-it causes incredible grief!

If you must, have separate date and time fields, which you can then reunite in a query.

Here's a 'laundry list' of little things you can do in the Debug (Intermediate) window to play with time issues:

'specify the test parameters
myin = #10:00#
myout = #11:22#

'Use the cdbl() function to display the test parameters as they are stored by MS Access
? cdbl(myin)
0.416666666666667
? cdbl(myout)
0.473611111111111

'use the DateDiff() function to return the difference between MyIn and MyOut in minutes
? datediff("n", myin, myout)
82

'show the decimal equivalent to the previous action
? datediff("n", myin, myout)/60
1.36666666666667

'isolate the number of hours between myin and myout (note the "\" integer operator - look it up if it's unfamiliar) datediff("n", myin, myout)\60
1

'isolate the number of minutes between myin and myout (note the MOD operator - look it up if it's unfamiliar)

? datediff("n", myin, myout) mod 60
22

'display the difference (as a string) in "hh:nn" format)
x = datediff("n", myin, myout)
? x\60 & ":" ltrim(x mod 60)
1:22

 

[ritchieroo]

Yes, as I pointed out the method I outlined does not work well when the dates are more than 24 hours apart like the example in Pat's response...

#3/1/2001 1:30# - #2/1/2001 00:45#

However, the values '28.03125' and '27/01/1900 00:45:00' are equivalent (assuming that you're measuring dates from midnight on 31/12/1899).

When the dates are less than 24 hours apart the method works well, take today's date...

CDate(#04/02/2002 08:00# - #04/02/2002 17:30#)

produces '09:30:00', i.e 9 and a half hours.


As for the really techy bit, I always assumed that the Date data type was fixed-precision (as is the Currency data type). Can anyone clarify this point?

Do we all agree that fixed-precision variables will not be subject to floating point errors during simple addition and subtraction operations (although they will suffer from round-off if we divide or multiply)?

 

[raskew]

The following article describes the issue in more depth.

support.microsoft.com/support/kb/articles/Q130/5/14.ASP

This article points out that a Date/Time data type is stored as a double-precision, floating-point number (up to 15 decimal places). - The integer portion of the double-precision number representing the date - The decimal portion representing the time.

Guess that my point would be that, while currently your scenario never lapses across more than one midnight, theoretically it could happen (e.g., suppose you were asked to track medical residents (who may pull 36 - 48 hours shifts) or military personnel whose shifts could run for an unknown number of hours before being relieved.).

If your solution is limited to not-more-than-two-days (one midnight) and you're then asked to extrapolate the solution to other scenarios, you're dead in the water, and have to go back and basically start from scratch to develop a solution that will allow multiple days.

Point being, do it up front and save the grief if the scenario changes.

What do you think?

Best wishes,

Bob

 

[ritchieroo]

raskew,

Thanks for the feedback about storage. I thought that VBA went to fixed precision when the "hard" Date type was introduced to MSAccess rather than the Variant (date) subtype, but after running a few tests apparently not.

I noticed the MS article is pretty old, and still suggests using the CVDate function, instead of the CDate function.

I agree whole-heartedly with getting it right first time, and where my method falls down is in converting the underlying representation of the date difference into something meaningful for the end user... it obviously needs more work.

The value you get by simply subtracting one date from another is a useful and accurate representation of this situation. And can often be more useful than using the DateDiff function (which counts boundaries, i.e. midnights) and can return different results depending on when you start... For instance,

Datediff("d",#04/03/2003 23:59:00#, #04/04/2003 00:01:00#)

tells us that there is 1 day between these two dates, in reality the dates are only 2 minutes apart, but they span midnight.

Here are some more debug examples. These first two equations compare two situations with dates that are 2 mintutes apart.

?#04/04/2003 00:01:00# - #04/03/2003 23:59:00#
1.38888889341615E-03

?#04/04/2003 00:03:00# - #04/04/2003 00:01:00#
1.38888888614019E-03

At first glance things don't look good: the results are very slightly different. The key comes in using the appropriate date/time functions to extract the bits we want.

?Minute(#04/04/2003 00:01:00# - #04/03/2003 23:59:00#)
2

?Minute(#04/04/2003 00:03:00# - #04/04/2003 00:01:00#)
2

 

[doulostheou]

If anyone wants the full URL to the paper Pat Hartman referenced above, it is here: http://www.fmsinc.com/tpapers/math/index.html

I'm a little confused by the very last post on this thread. If you wanted to know the minutes between the two times, why did you just not use:

Datediff("n",#04/03/2003 23:59:00#, #04/04/2003 00:01:00#)

 

[ritchieroo]

clarification...

doulostheou,

DateDiff counts interval-boundaries not elapsed periods, try these:

Datediff("n", #04/04/2003 00:01:59#, #04/04/2003 00:02:00#)

Minute(#04/04/2003 00:02:00# - #04/04/2003 00:01:59#)

The first example below returns 1, whereas the second example returns 0.