Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

adding hours past 24

Discussion in 'Information Technology' started by ReportSmith, Jul 28, 2006.

Tags:
  1. ReportSmith

    ReportSmith
    Expand Collapse
    Guest

    I have a few data files that are imported from another system (that I can't
    access/modify) into an Access application I put together. The files contain
    a few fields with data values in 'Short Time' format (ex: 0:45 or 8:30).

    I know there is no easy way of adding time values past 24 hours (or is
    there?). I'm forced into adding the values using something like:

    [FieldX] & ":" &
    if(([FieldX]-Int(FieldX])*60)<10,"0"&([FieldX]-Int([FieldX])*60,([FieldX]-Int(FieldX])*60))

    where [FieldX] is the sum of time values. But what if the sum is > 24?

    I've read numerous posts on dealing with time values, but keep going back to
    my lengthy (almost incomprehensible) formulas (but they do work).

    I was thinking of converting the time values to general numbers, doing the
    math and then reconverting back to hh:mm. If so, what's the best way?

    Thanks for any help anyone can provide.
     
  2. Loading...

    Similar Threads Forum Date
    Ashdoc's Movie Review---the Finest Hours Theatre, Movies & Cinema Feb 6, 2016
    about 8 hours! Blogs Oct 16, 2015
    Punjab entrepreneur sets up 10-storey building in Mohali in 48 hours Punjab, Punjabi, Punjabiyat Dec 3, 2012
    Kindly put some light on final hours of Guru Gobind Singh Jee's Human Life. Sikh Sikhi Sikhism Jul 29, 2012
    Bhagat Singh's Final Hours History of Sikhism Oct 14, 2011

  3. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    The problem is that the Date/Time functions deal with real dates and times.
    Elapsed times, although frequently formatted to look like times, aren't. For
    example, an elapsed time of 3:28 is three hours and 28 minutes, not 3:28 AM.

    To add elapsed times, you'll have to break them apart then add them. You can
    multiply the hours by 60 and add that to the minutes then add up the minutes
    or you can add the hours and minutes separately. Either way, once you've
    done the addition, you'll have to break the minutes up into hours and
    minutes (or days, hours, minutes or whatever you prefer) and, if you used
    the first method, add the hours to the sum of hours you came up with.

    I find it easiest to convert everything to the smallest unit I want in the
    output, add up that, then format back into the way I want it displayed (i.e.
    the second method listed above). Built-in formats won't work without some
    help. When you split things back up, it is frequently easier to format it
    using concatenation:

    Example:
    lngHours & ":" & lngMinutes

    This will break second up into Hours:Minutes:Seconds

    Public Function HHMMSS(lngSeconds As Long) As String
    Dim lngTest As Long
    HHMMSS = lngSeconds \ 3600 & Format((lngSeconds Mod 3600) / 86400, ":nn:ss")
    End Function

    --
    Wayne Morgan
    MS Access MVP


    "ReportSmith" <ReportSmith@discussions.microsoft.com> wrote in message
    news:22F4CC14-6B4B-416B-A95A-456102EA86C0@microsoft.com...
    >I have a few data files that are imported from another system (that I can't
    > access/modify) into an Access application I put together. The files
    > contain
    > a few fields with data values in 'Short Time' format (ex: 0:45 or 8:30).
    >
    > I know there is no easy way of adding time values past 24 hours (or is
    > there?). I'm forced into adding the values using something like:
    >
    > [FieldX] & ":" &
    > if(([FieldX]-Int(FieldX])*60)<10,"0"&([FieldX]-Int([FieldX])*60,([FieldX]-Int(FieldX])*60))
    >
    > where [FieldX] is the sum of time values. But what if the sum is > 24?
    >
    > I've read numerous posts on dealing with time values, but keep going back
    > to
    > my lengthy (almost incomprehensible) formulas (but they do work).
    >
    > I was thinking of converting the time values to general numbers, doing the
    > math and then reconverting back to hh:mm. If so, what's the best way?
    >
    > Thanks for any help anyone can provide.
     
  4. ReportSmith

    ReportSmith
    Expand Collapse
    Guest

    Thank you Wayne.
    I also came to the same conclusion about splitting the time and recombining
    it. I actually also have a formula in the same report that takes the time
    down to minutes and then brings it back as hh:mm. It just seemed like I was
    missing something - guess not.

    I'll keep plugging at the the report and try to have some sort of
    consistency with the formulas - maybe have all of them go down to minutes and
    then back to hh:mm - right now it's all over the place, with temp variables
    in invisible text boxes and in the master query on which the report is built.

    Again, thanks.

    "Wayne Morgan" wrote:

    > The problem is that the Date/Time functions deal with real dates and times.
    > Elapsed times, although frequently formatted to look like times, aren't. For
    > example, an elapsed time of 3:28 is three hours and 28 minutes, not 3:28 AM.
    >
    > To add elapsed times, you'll have to break them apart then add them. You can
    > multiply the hours by 60 and add that to the minutes then add up the minutes
    > or you can add the hours and minutes separately. Either way, once you've
    > done the addition, you'll have to break the minutes up into hours and
    > minutes (or days, hours, minutes or whatever you prefer) and, if you used
    > the first method, add the hours to the sum of hours you came up with.
    >
    > I find it easiest to convert everything to the smallest unit I want in the
    > output, add up that, then format back into the way I want it displayed (i.e.
    > the second method listed above). Built-in formats won't work without some
    > help. When you split things back up, it is frequently easier to format it
    > using concatenation:
    >
    > Example:
    > lngHours & ":" & lngMinutes
    >
    > This will break second up into Hours:Minutes:Seconds
    >
    > Public Function HHMMSS(lngSeconds As Long) As String
    > Dim lngTest As Long
    > HHMMSS = lngSeconds \ 3600 & Format((lngSeconds Mod 3600) / 86400, ":nn:ss")
    > End Function
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "ReportSmith" <ReportSmith@discussions.microsoft.com> wrote in message
    > news:22F4CC14-6B4B-416B-A95A-456102EA86C0@microsoft.com...
    > >I have a few data files that are imported from another system (that I can't
    > > access/modify) into an Access application I put together. The files
    > > contain
    > > a few fields with data values in 'Short Time' format (ex: 0:45 or 8:30).
    > >
    > > I know there is no easy way of adding time values past 24 hours (or is
    > > there?). I'm forced into adding the values using something like:
    > >
    > > [FieldX] & ":" &
    > > if(([FieldX]-Int(FieldX])*60)<10,"0"&([FieldX]-Int([FieldX])*60,([FieldX]-Int(FieldX])*60))
    > >
    > > where [FieldX] is the sum of time values. But what if the sum is > 24?
    > >
    > > I've read numerous posts on dealing with time values, but keep going back
    > > to
    > > my lengthy (almost incomprehensible) formulas (but they do work).
    > >
    > > I was thinking of converting the time values to general numbers, doing the
    > > math and then reconverting back to hh:mm. If so, what's the best way?
    > >
    > > Thanks for any help anyone can provide.

    >
    >
    >
     

Share This Page