Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a report with the following fields

    [signed on]
    [personel]

    both are formatted h:nn:ss

    what I am trying to do is calc the percent of time which is spent in [personel] as it relates to the overall time [signed on]

    I have tried making a field = [personel] / [signed on] formatted percent

    but this does not work all I get is 0.00%

    ex: [signed on] 0:10:00 and [personel] 0:02:30 should give you a percent of 25%

    I am guessing it is because I am trying to do the math on times and not general numbers.

    Any help would be appreciated.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by cslauenwhite View Post
    I have tried making a field = [signed on] / [personel] formatted percent
    Times are just stored as numbers, so you can do arithmetic with them. (things get more complicated if you have times exceeding 24 hrs)

    From you description, you have this the wrong way around.

    Try: = [personel] / [signed on] formatted percent
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Times are just stored as numbers, so you can do arithmetic with them. (things get more complicated if you have times exceeding 24 hrs)

    From you description, you have this the wrong way around.
    Thanks I noticed that just as I read your message....

    as for the result..... I have tried that formula (the right one ) and the result is not right here is what I have....

    [Signed on] 16:47:11
    [personal] 00:11:24

    When I do this is excel to check my work 1.13% the correct answer as per other reports I am working from
    when I do this is access

    =[Personal]/[Signed On] formatted as percent answer I get back as 2.29%

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When I do the calculation in Access i get the same answer as I (and you) get in Excel. Here it is done in a query.
    [attachment=89137:worjours.gif]


    Can you post a demo version of the db.
    Attached Images Attached Images
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,

    I think I solved the issue. I attacked the formula from the query insted of on the report and it worked great. I dont know why the report version did not function but it is working now.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by cslauenwhite View Post
    I dont know why the report version did not function but it is working now.
    I don't know why it makes any difference either, but the important thing is that you now have something that works.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,

    Sometimes I have learned not to ask questions when something start to work... lol

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Times are just stored as numbers, so you can do arithmetic with them. (things get more complicated if you have times exceeding 24 hrs)

    Things just got more complicated as I have times exceeding the 24 Hr mark.... They are just showing a time because the date changed.... How can I get it to show

    100:25:00
    h:mm:ss

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Access will never display a time in the way you want (unlike Excel, which will if you set the correct format...I forget just what it is).
    You can perform a whole lot of calculations to break the time into three separate amounts: hours, minutes and seconds as three separate numbers, then display the three separate values. But it is a fair bit of work.

    Dates and Times are stored in the same datatype, with the whole number part representing the days, and the decimal bit the time component. So once the time biit goes over 24 it just becomes a day.
    So you could display it as days, hours, minutes and seconds.

    But the calculation to calculate the percentage will still be correct.

    When I need to work with times exceeding 24 hours, I just use a double variable type to represent the number of hours. So 27.5hrs means 27 hrs 30 minutes.
    If I am calculating this from a Start and Finish Time, you need to multiply the difference by 24 to make the conversion.

    To take this further, we would need to know more about where the times come from..how they get entered or calculated, and what you want to do with them.
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,
    the excel format you'd be looking for is [h]:mm:ss (the square brackets are the key)

    If you need to show negative times, you need to change to the 1904 date format in Excel.

    In Access, try this as a control source in your report:

    =int(24*[signed on])& ":" &int(((24*[signed on])-int(24*[signed on]))*60)& ":" &int((24*60*[signed on]-int(24*60*[signed on]))*60)

    Jim

Posting Permissions

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