Results 1 to 5 of 5

Thread: Excel formula not working for overtime calculation

  1. #1
    New Member
    Join Date
    Nov 2018
    Location
    Vilanculos
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel formula not working for overtime calculation

    Good day,

    I am struggling to enter a formula into Excel.

    We do our employees time sheets on Excel. I am trying to automate the calculation of the absent days, off days and overtime due.
    I have gotten the formula correct for the absent days, off days and overtime.

    There is just one issue that I can not resolve.

    We don't want the employees to get penalized on their overtime if they have any absent or off days. I can not get the formula to calculate it correctly.

    I want to enter the following formula into AJ4:
    =(SUM(D4:AG4))+(AH4*8.5)+(AI4*8.5)
    note: the 8.5 is the normal working hours per day

    If I type in the above formula the answer is completely wrong.

    Please can someone help me.

    Thank you in advance!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Timesheet png.jpg 
Views:	212 
Size:	15.3 KB 
ID:	7648  
    Attached Files Attached Files

  2. #2
    Diamond Member Neville Bailey's Avatar
    Join Date
    Nov 2010
    Location
    Westville, Durban
    Posts
    2,760
    Thanks
    44
    Thanked 451 Times in 396 Posts
    If I type the above formula into cell AJ4, I get #VALUE! as an answer.

    The reason for this is that AH4 and AI4 are referring to text values and not numeric values, i.e. F and OFF.
    Neville Bailey - Sage Pastel Accounting Consultant
    www.accountingsoftwaresupport.co.za
    neville@accountingsoftwaresupport.co.za
    IronTree Online Solutions

    "Give every person more in use value than you take from them in cash value."
    WALLACE WATTLES (1860-1911)

  3. #3
    New Member
    Join Date
    Nov 2018
    Location
    Vilanculos
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HI Neville,

    I have had to do a lot of formatting edits to be able to get this far.

    If I type in the above formula I get an answer of 1011:30, which is totally wrong. The correct answer should be 221. I have no idea how to correct the formula or cell formatting so that it works.

  4. #4
    Gold Member
    Join Date
    Feb 2016
    Location
    Johannesburg
    Posts
    713
    Thanks
    74
    Thanked 199 Times in 178 Posts
    SUM(D4:AG4) means you are summing the dates?

  5. #5
    Bronze Member michellepace's Avatar
    Join Date
    Oct 2018
    Location
    South Africa
    Posts
    131
    Thanks
    15
    Thanked 3 Times in 3 Posts

    Lightbulb

    Hi Helizelb,

    It will be a lot easier if you work with numbers (eg "8.5" hours) instead of "time" 08:30. I've amended your worksheet and attached it for you. You'll still need to work out the "1.5" and "2" columns.

    Also, I made your dates calculate by themselves. You jus tneed to put in the Starting date in cell C19.

    Copy of Timesheet.easier.xlsx

Similar Threads

  1. [Question] Formula to calculate leave due in Excel
    By Helizelb in forum Accounting Forum
    Replies: 13
    Last Post: 20-Nov-22, 12:40 PM
  2. Vat formula not calculating correctly in Excel
    By NVRensburg in forum Accounting Forum
    Replies: 4
    Last Post: 07-Feb-18, 01:25 PM
  3. Calculation of overtime for monthly salaried employees on different shifts
    By southafricanrob in forum Labour Relations and Legislation Forum
    Replies: 0
    Last Post: 23-Mar-17, 12:35 PM
  4. Working Hours & Overtime - Fuel transport / Trucking
    By SSS100 in forum Labour Relations and Legislation Forum
    Replies: 1
    Last Post: 16-Dec-11, 06:37 AM
  5. Overtime Calculation
    By Yvonne in forum General Business Forum
    Replies: 3
    Last Post: 02-Sep-08, 07:50 AM

Tags for this Thread

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

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