Results 1 to 4 of 4
Thread: Date Diff Return Problems

070306, 19:58 #1Registered User
 Join Date
 Aug 2005
 Posts
 4
Unanswered: Date Diff Return Problems
I have an issue with DateDiff. Is as follows.
I have two dates. Examples are:
CreationDate = 17/05/2006 2:03:34 PM
CompletionDate = 19/05/2006 2:30:20 PM
I need to work out the difference between these two values and assign it to a field that I use in different calculations (eg Max, Min, Avg etc)
The calculations can work fine when I use a date differentiation and return the hours, mins and seconds....but HOW do i return the days as well???
Most dates are within 1 day of eachother, but once the hours returned is more than 23, it flips out, so i want to add days in as well. Thought I could format it like dd:hh:nn:ss but it doesnt work.
Can anyone help?
Once I can show days, hours, mins and seconds, I need to be able to still perform basic math calcs on them....

070306, 21:31 #2Registered User
 Join Date
 Apr 2004
 Location
 outside the rim
 Posts
 1,011
Originally Posted by Help File

070406, 21:03 #3Registered User
 Join Date
 Aug 2005
 Posts
 4
You must think Im really stupid haha.
I already know how the DateDiff function works, to return days, hours, mins, seconds or any givin variant, but my problem is making one field show the days, hours, mins and seconds all as one value and still being able to perform domain aggregate functions on them.

070506, 08:10 #4Registered User
 Join Date
 Apr 2004
 Location
 outside the rim
 Posts
 1,011
You must think Im really stupid
I was answering the question:Originally Posted by mc_tuppz
The easiest to work with would be a number result. Since the return value is a Long, you don't get decimals. So, the only way to be close to accurate using numbers is to return the value in seconds and then convert to days, hours and minutes. This would easiest be done with a custom function in code:Code:Public Function fnDateDiff(dt1 As Date, dt2 As Date) As String ' There are many ways to do this, here is an example of one. ' USe varients of this functions to strip portions of the date difference Dim d As Long Dim h As Long Dim m As Long Dim s As Long s = DateDiff("s", dt1, dt2) ' the total number of seconds between the dates m = Fix(s / 60) ' the number of whole minutes s = s Mod 60 ' the number of remaining seconds h = Fix(m / 60) ' the number of whole hours m = m Mod 60 ' the number of remaining minutes d = Fix(h / 24) ' the number of whole days h = h Mod 24 ' the number of remaining hours fnDateDiff = d & "D " & h & "H " & m & "M " & s & "S" End Function
For Seconds only, use: =DateDiff("s",[dt1],[dt2]) Mod 60
For Minutes only, use: =DateDiff("m",[dt1],[dt2]) Mod 60
For Hours only, use: =DateDiff("h",[dt1],[dt2]) Mod 24
and for days, use: =DateDiff("d",[dt1],[dt2])
To put it together into one number being the decimal number of days, use:
=[txDay] + [txHr]/24 + [txMin]/1440 + [tSec]/34560
Another option would be to leave the values as dates. Domain functions will work with dates (to the software, a date is just a formatted number anyway). Unfortunately, Access doesn't let you operate directly on dates the same way Excel does, so this would require extra steps.
Many ways to do this, these are just some examples.
Have fun,
tc