+ Reply to Thread
Results 1 to 5 of 5

Add an amount to wages if Overtime worked, but show zero if there is no overtime

  1. #1
    Registered User
    Join Date
    02-13-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    26

    Add an amount to wages if Overtime worked, but show zero if there is no overtime

    If overtime is worked then it is the overtime rate plus 10% of the hourly rate. I have put a column in the spreadsheet for the 10% amount and I have tried to add it on but when there are no entries in the spreadsheet it is showing the 10% amount still.
    Is there a simple solution to that to show zero if there is no overtime amounts?

    Spreadsheet has calculation as follows to calculate Normal, Time 1/2 & Double Time

    =SUM(AE13*B13)+(AF13*B13*1.5)+(AG13*B13*2)

    For Time and 1/2 and Double time I need it to add 10% of normal wage amount - eg: $17.30 is hourly so $1.73 needs to be added to the Overtime payments total.
    When I try to add it on it always shows the amount of $1.73 in the column even when there is no overtime.

    Any help would be much appreciated.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Add an amount to wages if Overtime worked, but show zero if there is no overtime

    Hi and welcome to the forum

    You need to tell us what each of those cells are.

    Having said that...
    1. you dont need to use =SUM() for that...
    =(AE13*B13)+(AF13*B13*1.5)+(AG13*B13*2)

    That (I think) would then become...
    =(AE13*B13)+if(AF13="",0,AF13*B13*1.5)+IF(AG13="",0,AG13*B13*2)

    This assumes that AF13 and AG13 are the 2 sets of OT hours, and also that if there is no OT, they will be blank. If theuy will contain 0, then change the bolded part...=""...to...=0
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-13-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Add an amount to wages if Overtime worked, but show zero if there is no overtime

    Thank you for your response and the welcome.
    The formula is not quite working out. I have 3 calculation columns as per below. If a person works 10 hours in the day and they are casual then I need to add 25% to their Normal base rate for casual loading, then for all overtime hours 10% is added to that hourly figure.
    If the Base rate is $17.30 then the formula should add up to $234.24

    Normal Time 1/2 Double
    7.6 2.00 0.4

    I think I may have to put the amounts of each into their own column and reference them when multiplying the hours.
    Let me know if you think there is a better way.

    Thanks

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Add an amount to wages if Overtime worked, but show zero if there is no overtime

    Perhaps you missed this part?
    You need to tell us what each of those cells are.
    I suggest you attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    02-13-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Add an amount to wages if Overtime worked, but show zero if there is no overtime

    Thank you for your help but I will just have the total cell get the values from a hidden cell that has the proper amount in it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating Overtime (after 40 hours worked)
    By YolandaJ55 in forum Excel General
    Replies: 4
    Last Post: 04-05-2015, 04:59 PM
  2. Calculating total overtime hours worked per employee
    By markjmcl in forum Excel General
    Replies: 3
    Last Post: 09-07-2011, 06:49 PM
  3. Track weekly overtime worked and available ytd
    By excelorfail in forum Excel General
    Replies: 3
    Last Post: 12-29-2008, 12:23 AM
  4. Overtime Wages
    By derekg1023 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-05-2006, 08:48 AM
  5. I need a continuous 30 day calendar to track overtime worked.
    By William in Denver in forum Excel General
    Replies: 1
    Last Post: 03-27-2006, 02:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1