Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: How to convert minutes into hours in report

    Hey there all,

    I have a tracking database I have been working on. What I am trying to do is have the totals =. Ex. 1.25 + 3.50 would give Grand total of 4.15 not 4.75. Thew table has a field called time spent which stores the time. I have attached an example database.
    Thanks for any help and your time.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jinro View Post
    Hey there all,

    I have a tracking database I have been working on. What I am trying to do is have the totals =. Ex. 1.25 + 3.50 would give Grand total of 4.15 not 4.75. Thew table has a field called time spent which stores the time. I have attached an example database.
    Thanks for any help and your time.
    I can't open your attachment; I assume it's a 2010 file since it won't open in 2007. And to be honest, you've really given very little detail, here.

    Is, for instance, 1.25 supposed to represent one and a quarter hours, or one hour and twenty-five minutes, or what?

    A little more explanation is really called for, in order for us to help you.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2013
    Posts
    4
    Yea sorry about that, I just attached a new one that can be open with earlier versions.

    The 1.25 was represent 1 hour and 25 minutes.
    so 1.35 would be 1 hour and 35 minutes and so on.
    What I am hoping to do is for people to track there work rather it takes them 10 minutes .10, 10 hours and 30 minutes 10.30 and for it all to add in the report at the end for a grand total.
    So if I had
    1.25
    .45
    1.5
    it should = 4.0 not 3.2
    Hope that helps explain. Been working on this for the past 10+ hours its driving me nuts only piece holding me back from being done.
    When you run the report you will see exactly what i am talking about how the grand total is = 16.65 when it should be 17.5.

    Thanks for your time
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2013
    Posts
    4
    Also if there is a better way of tracking the minutes and hours by all means i am open to suggestions, my way does not need to be the way. just want a way for this to work

  5. #5
    Join Date
    Apr 2013
    Posts
    4

    not sure if replied correctly sorry if posted twice

    Yea sorry about that, I just attached a new one that can be open with earlier versions.

    The 1.25 was represent 1 hour and 25 minutes.
    so 1.35 would be 1 hour and 35 minutes and so on.
    What I am hoping to do is for people to track there work rather it takes them 10 minutes .10, 10 hours and 30 minutes 10.30 and for it all to add in the report at the end for a grand total.
    So if I had
    1.25
    .45
    1.5
    it should = 4.0 not 3.2
    Hope that helps explain. Been working on this for the past 10+ hours its driving me nuts only piece holding me back from being done.
    When you run the report you will see exactly what i am talking about how the grand total is = 16.65 when it should be 17.5.

    Thanks for your time
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jinro View Post

    ...if there is a better way of tracking the minutes and hours by all means i am open to suggestions...
    Most of your problem, of course, is caused by the unusual way your users have to enter their time, using the

    Hour.Minutes

    format. Don't know where in the world you are, but this is not a recognized format in the US, and from a practical standpoint, my guess would be that some users entering

    1.25

    would mean

    1 hour and 25 minutes

    and other would enter the same thing when they mean

    1 hour and 15 minutes, i.e. one and a quarter hours.

    In your Table you really should either have one Field, where only minutes are entered, or one Field, where hours and fractional parts of hours are entered, or two Fields, one for Hours and one for Minutes. You'd follow this through by having the related Controls or Controls, on the Form, and doing calculations from any of these would be a snap!

    Quote Originally Posted by jinro View Post

    ...1.25 was represent 1 hour and 25 minutes...
    ...1.35 would be 1 hour and 35 minutes and so on...
    Even after the above explanation, the exemplar numbers you give and the results you want simply don't add up, literally!

    If

    1.25 was represent 1 hour and 25 minutes

    and

    1.35 would be 1 hour and 35 minutes

    then

    1.25 would be

    1 hour and 25 minutes

    .45 would be

    0 hours and 45 minutes

    1.5 would be

    1 hour and 5 minutes

    and the total would be

    2 hours and 75 minutes

    which is

    3 hours and 15 minutes

    not 4.0 nor 3.2! You really need to get these basic things straight before you get into the mechanics of adding the time up.


    The fact is that although you have TimeSpent defined as a Number, and it lets you enter data, like 1.25, because it looks like a Number, it (TimeSpent) really has to be treated as a String, and parsed in order to get any usable numbers out of it!

    To parse the Hours would be something like

    Code:
    =Val(Left([TimeSpent],InStr([TimeSpent],".")-1)))


    To parse the Minutes would be

    Code:
    =Sum(Val(Mid([TimeSpent],InStr([TimeSpent],".")+1)))


    Linq ;0)>
    Last edited by Missinglinq; 04-02-13 at 14:21.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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