Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Location
    Iowa
    Posts
    13

    Question Unanswered: Still Struggling

    I'm adding times for a time sheet project...

    In the report, it is adding correctly in Short Time Format, but once it gets to a total of over 24:00, it start back at zero???

    What number format must i use to add times, but have large values as totals, keeping in mind that i have to have the times I'm adding in date/time format...

    Thanks in advance

    BEN

  2. #2
    Join Date
    Jun 2004
    Location
    Iowa
    Posts
    13

    Question one more thing

    So in this report problem, I'm basically asking if it is possible to add multiple, short time formatted, times and recieve a general number with two decimal places...

    4:00
    5:00
    +1:30

    =10.5

    thanks again...

    BEN

  3. #3
    Join Date
    Jun 2004
    Location
    Iowa
    Posts
    13

    Impossible???

    Is adding times to equal a number impossible???

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am not sure where you are doing this but in code have alok at this, it may give you some clues to what is possible.

    Code:
    Sub ADDTimes()
        Dim Time1 As Date
        Dim Time2 As Date
        Dim Time3 As Date
        Dim NewTime As Date
        
        
        Time1 = "04:00"
        Time2 = "05:00"
        Time3 = "1:30"
        
        NewTime = Time1 + Time2 + Time3
        
        MsgBox "This time " & NewTime & " as a number = " & Format(NewTime, "#0.0")
        'ie = 10.5/24 to one decimal place.
        
    End Sub
    MTB

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...as Mike's code illustrates: there is no such access-thing as a time, only a datetime. just cos you've got it formatted to display as a short time doesn't change the internals. datetime is a number (a double as it happens) which holds days-point-fractionalDays

    when you add 12 hours to a datetime = 9.5 you get 10.0 which displays as 00:00:00 when formatted as a time.

    sooooo, you CANNOT add "times" (because they don't exist). you must add datetimes. if there is the chance of your result being >= 24 hours you must handle the date (days) componant of the datetime.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    ..as izy pointed out the datetime is a number with the decimal representing the 'time of day', therefore if you just want the time part subtract the integer of the datetime as below

    Code:
    Sub ADDTimes()
        Dim Time1 As Date
        Dim Time2 As Date
        Dim Time3 As Date
        Dim NewTime As Date
        
        
        Time1 = "04:00"
        Time2 = "05:00"
        Time3 = "21:30"
        
        NewTime = Time1 + Time2 + Time3
        
        MsgBox "This time " & NewTime & " as a number = " & Format(NewTime, "#0.0")
        '= 1.3 (ie = 10.5/24 to one decimal place.)
        
        NewTime = NewTime - Int(NewTime)
        MsgBox NewTime  'DISPLAYS ONLY THE 'TIME' PART IN HH:MM:SS FORMAT
        
    End Sub
    MTB

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Mike - i read the original problem as not being able to count up to 47:55:30 and higher when access rolls the day just after 23:59:59

    9 hours + 9 hours + 9 hours = 3 hours 'when displayed as time format.
    12 hours + 6 hours + 6 hours = 0 hours 'when displayed as time format.



    here are two possibilities:

    24 * datetime = hours-point-fractional hours (if you can live with fractional hours)



    otherwise construct a string in code.

    here is one way of doing it:

    strHHMM = cstr((1440*datetime)\60) & ":" & cstr((1440*datetime mod 60)\1)
    will get you to hh:mm where hh goes on increasing.

    a similar principle will get you to hh:mm:ss if you need seconds

    \ is deliberate (it wont work with / )


    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Isy

    If the datetime additions are all hours/minutes and the assumed starting time/date is zero then you are absolutely right if the total accumulated house are required.

    To get your strHHMM to format the minutes correctly (to nearest minute) I had to mod your code as follows

    strHHMM = (1440 * datetime) \ 60 & ":" & Format((1440 * datetime Mod 60) \ 1, "00")


    Cheers

    MTB

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Your date/time calculations should work fine if you keep the underlying data in the full date and time. Just set the format of the control to display the short date.

    To understand how Access (and the rest of Office) handles dates, open the attached spreadsheet.

    Have fun
    Attached Files Attached Files
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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