Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a field (lbstdate) where i enter date and time - ex 03/20/2009 10:00 AM
    I then enter end date and time (lbeddate) - ex 03/20/2009 11:30 AM

    With this Me.LbNomHour = DateDiff("h", [LbStDate], [LbEdDate]) I get 1.0 and i lost the half hour.

    with this Me.LbNomHour = DateDiff("n", [LbStDate], [LbEdDate]) I get 90.0 which means i have the half hour.

    What i want to see is 1.5

    How do i make that happen??

    Thanks in advance for any thoughts you might have.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,481
    Thanks
    3
    Thanked 41 Times in 41 Posts
    In that case I believe you will have to do Me.LbNomHour = (DateDiff("n",[LbStDate],[LbEdDate]))/60. As far as I know these functions only return integer values.
    Wendell

  4. #3
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='766466' date='20-Mar-2009 20:09']In that case I believe you will have to do Me.LbNomHour = (DateDiff("n",[LbStDate],[LbEdDate]))/60. As far as I know these functions only return integer values.[/quote]


    thank you!!! works great!!

  5. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    An easier solution is to subtract the earlier date/time from the later, and multiply the result by 24.
    Date and times are just stored as numbers, with the days represented as whole numbers and the times by the dec points.
    Regards
    John



  6. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,324
    Thanks
    1
    Thanked 12 Times in 12 Posts
    You have to understand how DateDiff works. Depending on the interval you want, DateDiff returns the # of boundaries you crossed between the 2 date/times you specify.

    For example: The result of DateDiff("d", #1/1/09 23:00#, #1/1/09 23:59#) is 0.
    But if the 2nd date/time is only 2 minutes later, that is DateDiff("d", #1/1/09 23:00#, #1/2/09 00:01#), the result is 1.

    Notice there is only 61 minutes separating the last 2 date/times, but since a day boundary was crossed, the result is 1 day.

    Similarly, the result of datediff("m",#1/01/09#,#1/31/09#) is 0! There are 30 days between the 2 dates, but NO month boundaries.

    Remember, it is the # of boundaries crossed. This is why the result is always an integer, and why there is no "rounding".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,519
    Thanks
    0
    Thanked 22 Times in 22 Posts
    [quote name='MarkLiquorman' post='766562' date='22-Mar-2009 00:12']You have to understand how DateDiff works. Depending on the interval you want, DateDiff returns the # of boundaries you crossed between the 2 date/times you specify.

    For example: The result of DateDiff("d", #1/1/09 23:00#, #1/1/09 23:59#) is 0.
    But if the 2nd date/time is only 2 minutes later, that is DateDiff("d", #1/1/09 23:00#, #1/2/09 00:01#), the result is 1.

    Notice there is only 61 minutes separating the last 2 date/times, but since a day boundary was crossed, the result is 1 day.

    Similarly, the result of datediff("m",#1/01/09#,#1/31/09#) is 0! There are 30 days between the 2 dates, but NO month boundaries.

    Remember, it is the # of boundaries crossed. This is why the result is always an integer, and why there is no "rounding".[/quote]
    That's a great explanation, thank you.

    That kind of explanation really helps when trying to work these things out.

    Makes a lot of sense too regarding boundaries hence integer calculation.

Posting Permissions

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