# Thread: Date Diff Return Problems

1. Registered 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....

2. Registered User
Join Date
Apr 2004
Location
outside the rim
Posts
1,011
Originally Posted by Help File
DateDiff Function

Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
DateDiff("d",Date1,Date2) should do the trick

3. Registered 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.

4. Registered User
Join Date
Apr 2004
Location
outside the rim
Posts
1,011
You must think Im really stupid
No, I don't. I appologize if I offended you. Many people do post asking questions without first checking the help file or other readily available resources.

Originally Posted by mc_tuppz
I use a date differentiation and return the hours, mins and seconds....but HOW do i return the days as well?
For your more clarified question in the second post, to combine days, minutes, and seconds into one field you have several options, depending on how your domain aggregate functions are setup.

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```
Similarly, you can use the MOD operator to get just each portion, like so:
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•