Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Time stored in Hours:Minutes

    I have a field on a report called TotalTime it is the result of a calculation (timeend-timestart).

    The field is calculated with the time in hours and minutes eg 30 minutes = 00:30; two and a half hours = 02:30

    The field is formulated as a shorttime with the following mask 00:00;0;_

    This field shows how much time was spent with a client.

    My clients are grouped in the report.

    In one group when I try to sum the field (as in total the field) I get a figure that is not what I am expecting ie
    Client1 has a time of 1:00
    Client2 has a time of 3:00
    The answer given is 0.16666667 when I expected to see 4:00

    In another group the client3 has a time of 1:30 (1 and half hours) and the answer given is 0.0625

    can you advise how to fix this problem?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Karen Day View Post
    I have a field on a report called TotalTime it is the result of a calculation (timeend-timestart).

    The field is calculated with the time in hours and minutes eg 30 minutes = 00:30; two and a half hours = 02:30

    The field is formulated as a shorttime with the following mask 00:00;0;_

    This field shows how much time was spent with a client.

    My clients are grouped in the report.

    In one group when I try to sum the field (as in total the field) I get a figure that is not what I am expecting ie
    Client1 has a time of 1:00
    Client2 has a time of 3:00
    The answer given is 0.16666667 when I expected to see 4:00

    In another group the client3 has a time of 1:30 (1 and half hours) and the answer given is 0.0625

    can you advise how to fix this problem?
    0.16666667 indicates the decimal fraction of a day. 4 hours is 0.16666... of a day. It's telling the truth. Similarly, 1:30 is .0625 (1/16) of a day. Simply multiply the decimal by 24 and you'll get the answer you want. To change the format to 1:30, or 4:00, simply use the Format() function. Look it up in the Help file, and make sure to study the Examples given there. Beware, however, the results of the Format() function are String, not Numeric or Time. So, if you want to do additional math on the results, make sure you have two result boxes, one with the numerics, and one with the result of the Format() function (of course, you can hide one or the other).

    Good luck,
    SL

  3. #3
    Join Date
    May 2002
    Posts
    157

    Time Stored In hours and Minutes

    Thanks for the explanation.

    I worked it out but am just adding this to the forum, just in-case someone else has a similar problem.

    Ok just to review -
    I have a field on a report called [TotalTime] it is the stored result of a calculation (timeend-timestart). The stored result shows the time in hours and minutes eg 30 minutes = 00:30; two and a half hours = 02:30. To get this to show in hours and minutes, I formatted the field as a shorttime with the following mask 00:00;0;_ By the way the [TotalTime] field can get this time through a calculation or by simply entering the total time (with hours and minutes seperated by a colon( : )).

    I was performing a sum on this field by group (I called the field [SumTotalTime]). That is I was adding all the [TotalTimes] for each record in the group to end up the 'sum' of all times. This was giving me the unexpected answer I have previously described.

    After Sam's suggestion to read up about 'Format() function' - I think I got even more confused (after reading it all). BUT what Sam did explain was that the results I was getting was a "decimal fraction of a day".

    So I looked at the properties of the [SumTotalTime] field and changed the format and input mask of the field to end up with the following (which works!!!!!). Perhaps that is what Sam was telling me to do in the first place...


    (Text Box on report located in a group footer)
    NAME: SumTotalTime
    CONTROL SOURCE: =Sum([TotalTime])
    FORMAT: Short Time
    DECIMAL PLACES: Auto
    INPUT MASK: 00:00;0;_
    etc.....


    All the best and thanks Sam for your help.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're welcome. Glad I was able to help.

Posting Permissions

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