Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78

    Unanswered: Hour and Minutes Format

    I have a timesheet type system which holds times in hours and minutes for time spent on different jobs. For example Job1 Start 09:00 Finish 10:15. There will be many of these records and Job1 will have lots of records against it.

    I need to make a report which shows the total hours and minutes worked on Job1. Currently I total it up and it is formatted as short time but as soon as it totals to more that 24hours it will go around to 01:00, 02:00 etc.

    How do I get it to calculate so the total could for example be 320Hours 19 Minutes?

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    how are you currently working out the total?
    Please post the calculations/formulae
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Times are entered on the form as example Start Time 08:15 Finish Time 08:35. The total time is worked out by Finish Time minus Start Time example 08:35-08:15 gives us 00:20. However, it is not in the format 00:20 straight away it is stored as a number which in this case looks like 0.0138888888888889. If you then format that as short time or hh:nn it will show 00:20. It works ok for less than 24hrs but above that im not sure what to do.

    Hope that kind of explains it.

    Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So the time is not stored in a datetime format?
    That might cause some problems...

    Take a look at the DateDiff function:
    DateDiff(interval, date1, date2)
    DateDiff(n,<StartTime>,<FinishTime>)
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Thank you for that I will look at the datediff function.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Let us know how you get on
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Time sheet

    I had a similar task and solved it successfully. Here are some suggestions.

    1. I created a table named TimeIntervals
    It looks like this:
    TimeIntervalID Long
    TimeIntervalName text 11
    TimeLengthMinutes Single

    Then I populated this table with 82944 records.
    They look like this:

    TimeIntervalID TimeIntervalName TimeLengthMinutes
    1 00:00/00:05 5
    2 00:00/00:10 10
    3 00:00/00:15 15
    .........................................
    82942 23:55/23:45 1430
    82943 23:55/23:50 1435
    82944 23:55/23:55 1440

    This table is read only. Here we have all possible time intervals and time length related to any interval. The minimum time interval here is 5 min.

    Then we create another table related to this one. There we type other things like person, type of work and so on. We use a combo box to select a time interval.

    What we also need is to convert TimeLength into TimeFormat. For example 1020 min to convert into hh:mm format.

    Public Function ConvertLongMinutes2Time(sngMinutes As Long) As String
    On Error GoTo ConvertLongMinutes2Time_Err

    Dim hours, mins As Long
    ConvertLongMinutes2Time = "00:00"

    hours = Fix(sngMinutes / 60)
    mins = sngMinutes - (hours * 60)
    ConvertLongMinutes2Time = Format(hours, "#00") & ":" & Format(mins, "00")

    WrapUp:

    Exit_ConvertLongMinutes2Time:
    Exit Function
    ConvertLongMinutes2Time_Err:
    MsgBox Error.Description
    Resume Exit_ConvertLongMinutes2Time
    End Function

    There are also other functions that are used. The most chalenging think is to create a report about different activities time intervals and time lengths.

    We have to execite one of theese time related functions in a query. Because every line if the query executes several functions this query will not work if you have a lot of data.

    So what's the solution. Every function that deals with this time related functions amd math must work with the memory. Once the data is accessed from the disk it goes to memory.

    The functions are few and simple there is nothing special about them.
    Maybe this suggestion will give an idea.

  8. #8
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Thanks guys for you help. I used the datediff option and this seems to have worked. It took me a bit of time as I have a complex expressions and I had to add it to but it works.

    Thank you!!!

Posting Permissions

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