Error Checking in Excel
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: If, greater than, remove difference

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool If, greater than, remove difference

    Hello all,

    Here is what I am trying to accomplish - Auto populate overtime hours to another column. Here is the breakdown in writing -

    IF what is in this cell is GREATER THAN 40, REMOVE the amount that is GREATER and MOVE it to another column.

    I currently have a "REGULAR TIME" and "OVERTIME" column.
    In the OT column I did "=IFERROR(IF([Regular Hours]>40,[Regular Hours]-40,""),"")

    While is produces the correct number, I'm still left with the original hours worked in the REGULAR column (say 45), PLUS 5 hours in the OT column for a total of 50 hours.

    Is there a way to have it remove those 5 hours and place it in the OT column?

    Thanks!!!!!!!!

  2. #2
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,133
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If, greater than, remove difference

    you would need to use vba code to edit a cell's value based on a value that is in the same cell OR rename the column total hours and then you calculate the regular and overtime columns

    If you think about it... you start with total hours... why would try to change it and then calculate total hours again later??

    Overtime hours:

    Code:
    =MAX(TOTAL-40,0)
    Regular Hours:

    Code:
    =MIN(TOTAL,40)
    Last edited by cerfani; Mar 21st, 2018 at 01:41 PM.

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,660
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If, greater than, remove difference

    Is [Regular Hours] a calculation? If so, you can do this with two simple formulas:

    For regular hours column:
    =MIN([Regular Hours],40)

    For OT hours column:
    =MAX([Regular Hours]-40,0)

    If you use these formulas, you would need to move anything. It is built right into the formulas.
    Last edited by Joe4; Mar 21st, 2018 at 01:42 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Mar 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If, greater than, remove difference

    Quote Originally Posted by Joe4 View Post
    Is [Regular Hours] a calculation? If so, you can do this with two simple formulas:

    For regular hours column:
    =MIN([Regular Hours],40)

    For OT hours column:
    =MAX([Regular Hours]-40,0)

    If you use these formulas, you would need to move anything. It is built right into the formulas.
    It's manual entry. I add them in each day so, 10+10+10, etc. Is that what you mean?

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,660
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If, greater than, remove difference

    So, there are no hard-coded values over 40 hours? Any number showing over 40 is a calculated field?
    If that is true, they I think you should be able to incorporate my formulas without having to use any VBA.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,133
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If, greater than, remove difference

    vba seems overkill when she already knows total hours... just calc the reg and overtime but...

    It seems like OP is keeping a time sheet... there are tons of these templates already made online and many are free... unless you want to learn and make it yourself you can use an expert made timesheet to keep track of hours of employees... https://www.vertex42.com/ExcelTempla...-template.html

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If, greater than, remove difference

    Quote Originally Posted by Joe4 View Post
    So, there are no hard-coded values over 40 hours? Any number showing over 40 is a calculated field?
    If that is true, they I think you should be able to incorporate my formulas without having to use any VBA.

    No hard-coded values, but the MIN formula overrides my time keeping - so I'm daily (sadly), having to key in each person's time, so each day I'm adding in the cell the day's totals (8+8+8+8, etc.)

    Honestly, I think VBA would probably be what I need. What I want is once the total hits 40 hours (so say 5 days of 10 hours each day, added up like 10+10+10+10+10), the cell recognizes it and puts the remaining amount in the "OVERTIME" column.
    I'm taking a VBA class, so maybe one day. Lol. I'm sure I'll find a manual work around until then.

  8. #8
    Board Regular
    Join Date
    Mar 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If, greater than, remove difference

    Quote Originally Posted by cerfani View Post
    vba seems overkill when she already knows total hours... just calc the reg and overtime but...

    It seems like OP is keeping a time sheet... there are tons of these templates already made online and many are free... unless you want to learn and make it yourself you can use an expert made timesheet to keep track of hours of employees... https://www.vertex42.com/ExcelTempla...-template.html

    I completely agree. Sadly, it's a client requirement and I have zero say-so, so manually it is.

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,660
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If, greater than, remove difference

    Honestly, I think VBA would probably be what I need. What I want is once the total hits 40 hours (so say 5 days of 10 hours each day, added up like 10+10+10+10+10), the cell recognizes it and puts the remaining amount in the "OVERTIME" column.
    That is what my two formulas already do, inherently!

    Whatever you currently have for formulas for your "regular" and "overtime" hours, you should be able to replace those with formulas like mine that automatically cap Regular hours at 40 and put anything above that in Overtime.

    Have you tried it?
    Last edited by Joe4; Mar 21st, 2018 at 02:14 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Mar 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If, greater than, remove difference

    Quote Originally Posted by Joe4 View Post
    That is what my two formulas already do, inherently!

    Whatever you currently have for formulas for your "regular" and "overtime" hours, you should be able to replace those with formulas like mine that automatically cap Regular hours at 40 and put anything above that in Overtime.

    Have you tried it?
    Yes, sorry I stated what it did above, but I guess I didn't do it well.

    When I do the MIN formula in the "Regular Hours" column, when I enter my time, it overrides the formula. I'm going into each cell and adding up the totals. Is there a way to format the column and lock that in?

Some videos you may like

User Tag List

Tags for this Thread

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
  •