Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DateDiff calc (2003 (SP2))

    Silly question; how do you calculate the elapsed hours & minutes between 2 fields?

    I've been trying to use the DateDiff function, but I can't seem to get the minutes part sorted. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DateDiff calc (2003 (SP2))

    If the difference is always less than 24 hours, you can simply subtract one field from the other and set the Format property of the result to a time format (built-in such as Short Time or custom such as h:mm).

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DateDiff calc (2003 (SP2))

    Use DateDiff with n for the interval. This will tell you the number of minutes.

    Then divide this by 60 to get the hours, and use the remainder for the minutes.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff calc (2003 (SP2))

    Hans, unfortunately the period could well extend beyond 24 hrs. So I'm afraid that method won't always be correct.

    John, using your solution would (I believe) give me hours & decimal minutes, not good for some of our users.

    I did try this ([DateTime] being the 'start' & [closed] the 'end);
    ActDur: (DateDiff("h",[DateTime],[closed])) & ":" & (((DateDiff("n",[DateTime],[closed])-(DateDiff("h",[DateTime],[closed])-1)*60)))
    but this gives me a 60 minute result, on whole hours! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    All ideas welcome! <img src=/S/please.gif border=0 alt=please width=31 height=23>

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff calc (2003 (SP2))

    Cheers John, that did it. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    FYI here's what worked (for me);
    ActDur: (DateDiff("n",[DateTime],[closed])60) & ":" & (DateDiff("n",[DateTime],[closed]) Mod 60)

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DateDiff calc (2003 (SP2))

    If the difference was 500 mins, then divide by 60 gives 8 hours, with a remainder of 20. The 20 means 20 mins.

    By remainder I don't what comes after the decimal point.
    added later

    You can use the operators / and mod to give you the two bits you need.

    500/ 60 = 8

    500 mod 60 =20

    oops..I wrote the wrong slash in this. It should be so 50060 = 8
    Regards
    John



Posting Permissions

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