Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time after Time (A2K)

    I realize that this particular subject has been revisited many times and the good news is that I have the correct
    answer. Almost. The bad news and/or question is: there has to be a neater way of doing this possibly in one calculation.

    I'm looking for the elapsed days, hours and minutes between two dates & times using date/time fields, eg

    Start Date: Sunday, February 15th, 2004 @ 5:16 pm
    End Date: Wednesday, March 10, 2004 @ 6:19 am

    I know the following to be true:

    The time calculated manually using all fingers, etc, is: 23 days, 13 hours, 4 minutes

    =Int([End Date]-[Start Date]) = 23 days
    =Int(([End Date]-[Start Date])*24) = 565 hours
    =Int(([End Date]-[Start Date])*1440 Mod 60) = 4minutes

    It's getting the 13 hours out of all of this that's giving me the problem

    What, besides sanity, am I missing? Hopefully my dumb as a post question may help someone else as well.
    Thanks in advance for any help.
    Cheers,
    Andy

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

    Re: Time after Time (A2K)

    =DateDiff("h", [Start Date], [End Date]) Mod 24
    or
    =Int(([End Date]-[Start Date])*24) Mod 24

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time after Time (A2K)

    Hans,

    Consider yourself having had a coffee bought for you. Thanks much.
    Cheers,
    Andy

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

    Re: Time after Time (A2K)

    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>
    If you're lost you can look--and you will find me
    Time after time
    If you fall I will catch you--I'll be waiting
    Time after time

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time after Time (A2K)

    I am trying to do this same thing in my database. But, I'm missing something. My start date is 4/7/2004 11:30AM and my end date is 4/7/2004 2:00pm (both formatted as dates). When I calculate the diff using your example, the result is -3.00.

    What am I missing? I obviously have no experience calculating dates/times in Access.
    Thank you!

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time after Time (A2K)

    Do you use :
    =DateDiff("h", [Start Date], [End Date]) Mod 24
    and not
    =DateDiff("h", [End Date], [Start Date]) Mod 24
    The first date should be the smallest
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time after Time (A2K)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I can't believe you quoted that!
    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Time after Time (A2K)

    That song is from before you were born, young man!

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time after Time (A2K)

    I know. And I know the song, <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I actually like it...it was just funny to see <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Time after Time (A2K)

    You have obviously been listening to me <img src=/S/sing.gif border=0 alt=sing width=24 height=20> at a piano bar somewhere - or you have lots of old pop standards from the 40s, 50s and 60s lying around! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Wendell

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

    Re: Time after Time (A2K)

    In fact, Time After Time was a hit for Cyndi Lauper in 1984; it was composed by her and Rob Hyman for her debut album She's so unusual.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time after Time (A2K)

    The confusion comes from the fact that there was a much earlier "Time After Time" from the 30s or 40s. Different lyrics entirely but the same title. <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>
    Charlotte

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Time after Time (A2K)

    As you can see, I'm from the "older generation" - my kids were listening to Cindi Lauper (maybe) when we lived in London.
    Wendell

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time after Time (A2K)

    Now, Wendell, you are my generation, and *I* listened to Cyndi Lauper in her early days. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Of course, I listened to Billy Joel, John Denver, bluegrass and classical music too, so that doesn't say anything about my musical tastes. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time after Time (A2K)

    I fixed it to the correct way, but it still shows only the hour... not the minutes. I need to know the time in hours and minutes. And, if the time lapse is over several days, I want the total hours and minutes (example: 36.45) .. the way I have it right now is

    start date 4/7/2004 11:30 AM
    end date 4/7/2004 2:0 PM

    DateDiff("h",[Start Date],[End Date]) Mod 24

    which returns 4.0

    I want it to return the total hours and minute

Page 1 of 2 12 LastLast

Posting Permissions

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