Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Time

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've been trying to create a formula to add my drivers hours. I enter them by
    day, and need a total for the week.

    Here is how they are keyed in:
    Monday Tuesday WednesdayThursday Friday TOTAL
    9.50 9.50 8.15 9.23 8.50 6.08

    Now when adding these times, if two times added together equal or go over 60
    then I have to add 40.

    Example 9.50 + 9.50 = 1900 "but I need to add 40" so my total should be 19.40
    Same with 9.23 + 9.40 = 18.63 "but I need to add 40" so my total should be 19.03
    But with 8.15 + 8.15 = 16.30 then that's fine

    I have a formula that works but it will not return my FALSE value. Here is the
    formula I have:

    =IF(SUM(A1:B1)>0.6,SUM(A1:B1)+.40,SUM(A1:B1))

    I've probably got you all confused, I know I am.

    Is there something wrong with that formula?

    Can you help???

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi!

    You will want to post your question in the correct forum. This is where people can test their signatures and things like that. Click on the link:

    http://www.mrexcel.com/board/viewforum.php?forum=2&1830=

    and post your question there. Just copy/paste it. Then, I'll take a look and many others will too!

    Welcome to MrExcel!
    ~Anne Troy

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-22 16:50, StacyRenee79 wrote:
    I've been trying to create a formula to add my drivers hours. I enter them by
    day, and need a total for the week.

    Here is how they are keyed in:
    Monday Tuesday WednesdayThursday Friday TOTAL
    9.50 9.50 8.15 9.23 8.50 6.08


    Supposing that A2:F2 houses the keyed in hours,

    in G2 enter: =SUM(A2:F2)

    And, this is important, custom format G2 as [hh]:mm.

    To custom format a cell,

    activate the cell;
    activate Format|Cells;
    choose Custom on the Number tab;
    enter in the Type box:

    [hh]:mm

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-24 00:02, Aladin Akyurek wrote:
    On 2002-02-22 16:50, StacyRenee79 wrote:
    I've been trying to create a formula to add my drivers hours. I enter them by
    day, and need a total for the week.

    Here is how they are keyed in:
    Monday Tuesday WednesdayThursday Friday TOTAL
    9.50 9.50 8.15 9.23 8.50 6.08


    Supposing that A2:F2 houses the keyed in hours,

    in G2 enter: =SUM(A2:F2)

    And, this is important, custom format G2 as [hh]:mm.

    To custom format a cell,

    activate the cell;
    activate Format|Cells;
    choose Custom on the Number tab;
    enter in the Type box:

    [hh]:mm
    Thanks but nope doesn't work!

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-24 05:19, StacyRenee79 wrote:
    On 2002-02-24 00:02, Aladin Akyurek wrote:
    On 2002-02-22 16:50, StacyRenee79 wrote:
    I've been trying to create a formula to add my drivers hours. I enter them by
    day, and need a total for the week.

    Here is how they are keyed in:
    Monday Tuesday WednesdayThursday Friday TOTAL
    9.50 9.50 8.15 9.23 8.50 6.08


    Supposing that A2:F2 houses the keyed in hours,

    in G2 enter: =SUM(A2:F2)

    And, this is important, custom format G2 as [hh]:mm.

    To custom format a cell,

    activate the cell;
    activate Format|Cells;
    choose Custom on the Number tab;
    enter in the Type box:

    [hh]:mm
    Thanks but nope doesn't work!
    What result do you get?

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-24 05:48, Aladin Akyurek wrote:
    On 2002-02-24 05:19, StacyRenee79 wrote:
    On 2002-02-24 00:02, Aladin Akyurek wrote:
    On 2002-02-22 16:50, StacyRenee79 wrote:
    I've been trying to create a formula to add my drivers hours. I enter them by
    day, and need a total for the week.

    Here is how they are keyed in:
    Monday Tuesday WednesdayThursday Friday TOTAL
    9.50 9.50 8.15 9.23 8.50 6.08


    Supposing that A2:F2 houses the keyed in hours,

    in G2 enter: =SUM(A2:F2)

    And, this is important, custom format G2 as [hh]:mm.

    To custom format a cell,

    activate the cell;
    activate Format|Cells;
    choose Custom on the Number tab;
    enter in the Type box:

    [hh]:mm
    Thanks but nope doesn't work!
    What result do you get?
    I get an off the wall result.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    [/quote]

    I get an off the wall result.
    [/quote]

    No wonder. I disregarded the fact that you enter times not as true times.

    In G2 enter:

    =(SUM(A2:F2)/24)*24

    Format G2 as General.

    For the values you provided:

    {9.5,9.5,8.15,9.23,8.5,6.08}

    I get:

    50.96

    Is this what you're looking for?

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeah I can do that but anything over 60 min. I need 40 added to that, in order for the value to be accurate.

    Ex. 9.50 + 9.50 = 19.00 but time wise you have to add 40 for the correct hours. You can't add 50min. and 50min. to get 100min. It's actually 1 hour 40 min.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    If you are trying to add up time, you really need to separate your hours and minutes with a colon instead of a dot. If you then format your cells as Aladin suggests [hh]:mm you can just add them up and get the correct answer.
    You can use the edit, replace function to change your dots to colons.
    Derek

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-24 17:30, StacyRenee79 wrote:
    Yeah I can do that but anything over 60 min. I need 40 added to that, in order for the value to be accurate.

    Ex. 9.50 + 9.50 = 19.00 but time wise you have to add 40 for the correct hours. You can't add 50min. and 50min. to get 100min. It's actually 1 hour 40 min.
    I don't believe I understand the logic you're using:

    9.50 is often used as another notation for

    9:30

    2 x 9:30 = 19:00 (or, in decimal notation, 19.00)

    By the way, it would help getting a direct answer, e.g., to what the result of 9.50 + 9.50 should be.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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