+ Reply to Thread
Results 1 to 6 of 6

Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

  1. 01-24-2013, 12:45 AM #1
    itsavvy
    itsavvy is offline
    Registered User
    Join Date
    01-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

    Hi, I need some help and advice here on the worksheet attached.
    I am trying to do 1 spreedsheet/template which I am able to use to fulfill different conditions on overtime as follows:

    (1) Employee A
    •Standard working hours are 8:00am - 5:00pm
    •Monthly Salary
    •Overtime : 5pm onwards @ 1.5 Rates

    (2) Employee B
    •Standard working hours are 8:00am - 5:00pm
    •Daily Salary
    •Overtime : 5pm onwards @ 1.5 Rates

    (3) Employee C
    •Standard working hours 8:00am - 5:00pm
    •Hourly Rated
    •Overtime : 5pm onwards @ 1.5 Rates

    (4) Employee D
    •Standard working hours 8:00am - 5:00pm
    •Weekly Salary
    •Overtime (1): 5pm - 12:00AM (midnight) @ 1.5 Rates
    •Overtime (2): 12:00am (Midnight) - 800am @ 2.0 Rates

    I managed to fulfill 1 - 3, however I am struggling to have the cells meet condition (4) to calucate the rates @ 2.0 after midnight. I hope the attached worksheet can explain what I am trying to do. Any advice or assistance is greatly appreciated. Thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-24-2013, 01:20 AM #2
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

    question..In sample, row 12 OT rate at 2 would be for 1/2 hour, or for the whole 2.5 hours ? In other words, is it just the part after midnight, or the whole part shift that occurs through midnight?
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post
    Reply With QuoteRegister To Reply
  3. 01-24-2013, 02:16 AM #3
    itsavvy
    itsavvy is offline
    Registered User
    Join Date
    01-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

    Hello it will be for 1/2 hour that occurs after midnight. Hence, OT rate @ 2.0 will be calcuated from 12 midnight to 8am.
    Reply With QuoteRegister To Reply
  4. 01-24-2013, 03:14 AM #4
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

    Okay, I have also adjusted Column J a bit, just to subtract any OT2 hours( shows at the end of J11 as
    -IF(ISNUMBER(K11),K11,0),"") the red part

    so...try this :Workers Timesheet-Example.xlsx

    Note- I left the zero values in OT 2 to make the formula easier to deal with, I suggest Conditional Formatting if you want them not to display

    Hope this helps
    Reply With QuoteRegister To Reply
  5. 01-24-2013, 04:58 AM #5
    itsavvy
    itsavvy is offline
    Registered User
    Join Date
    01-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

    Hi dredwolf,
    Thank you so much! This certainly works and helps a lot
    Reply With QuoteRegister To Reply
  6. 01-24-2013, 08:48 AM #6
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

    You are welcome
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1