Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Format ? (Y2K)

    In Excel there is a format option that returns the total number of hours, minutes and seconds, rather than what the time is, i.e. [h]:mm:ss. I normally use this when I need to know the difference between two dates and times, e.g. 09/06/06 11:03:35 minus 09/01/06 14:38:30 returns 01/04/1900 8:25:05 PM, which is formatted as 116:25:05, because the difference between the two dates and times is more than 24 hours.

    I imported data from an execel spreadsheet in to Access with a column of this data and format. In Access, I used format h:nn:ss in the table for this column, which works fine for time differences that are less than 24 hours; however, for values greater than 24, the total number of hours, minutes and seconds is not returned correctly (or at least how it's returned in Excel), e.g. 01/04/1900 8:25:05 PM is returned as 20:25:05, rather than 116:25:05.

    Any help provided would be appreciated.

    Scott

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

    Re: Time Format ? (Y2K)

    It would have been nice if Access had a cumulative time format like Excel, but unfortunately it doesn't.

    If you can live with decimal hours, e.g. 116.5 instead of 116:30, you can use an expression like
    <code>
    =24*([DateTime1]-[DateTime2])
    </code>
    Otherwise, you'll have to compute the hours, minutes and seconds separately:
    <code>
    =Int(24*([DateTime1]-[DateTime2]))
    </code>
    for the hours,
    <code>
    =Int(24*60*([DateTime1]-[DateTime2]) Mod 60)
    </code>
    for the minutes, and
    <code>
    =Int(24*60*60*([DateTime1]-[DateTime2]) Mod 60)
    </code>
    for the seconds. You can then concatenate these again in a string value, if you wish.

Posting Permissions

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