Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005

    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. #2
    Join Date
    Apr 2004
    outside the rim
    Quote Originally Posted by Help File
    DateDiff Function

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


    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. #3
    Join Date
    Aug 2005
    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. #4
    Join Date
    Apr 2004
    outside the rim
    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.

    I was answering the question:
    Quote 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:
    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,

Posting Permissions

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