+ Reply to Thread
Results 1 to 6 of 6

Calculating Overtime Hours from varying lists...

  1. #1
    Registered User
    Join Date
    08-19-2014
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Calculating Overtime Hours from varying lists...

    Hello!

    I am trying to calculate the Normal, OT and nightshift. hours based on lists I will try to explain here.. and I have attached a sample

    Formula for working hours in cell W6

    If cell G6 = CW1 (list from sheet 3) then * sheet 3 C26 by O6 but only if it is up to and including 10 hours

    I am unsure how to make the formula so it checks and returns for each of the CW rates on sheet 3??

    If the above can be achieved I will need a formula also for the Overtime in Cell X6 this will be based on the above but only for hours above 10 in cell O6.

    AND THEN! lol sorry

    The nightshift also needs the calculation but will need to recognize that nightshift has been selected in cell D7 then using the overtime rates from whichever CW has been chosen in column G

    Phew no wonder I am confused!!

    Thanks for your help
    Attached Files Attached Files

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Calculating Overtime Hours from varying lists...

    Got the solution..( I Hope...)
    Formula for Normal Hours:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula for OT Hours:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file..
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  3. #3
    Registered User
    Join Date
    08-19-2014
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculating Overtime Hours from varying lists...

    Hi

    W6 should equal $442 i.e. 10 hours at normal time from CW2 $44.20 sheet 3

    and

    X6 should equal $61.10 because it is only one hour overtime

    Just to note also that when nightshift is in effect it is only for anything over 8 hours in O6 Monday to Friday.

  4. #4
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Calculating Overtime Hours from varying lists...

    Okay... let me take a look..

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Calculating Overtime Hours from varying lists...

    I just mistakenly divided the result by a cell..
    New Formulas..
    Normal Hours
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    OT Hours
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file..
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,479

    Re: Calculating Overtime Hours from varying lists...

    For Normal 10 hours C column gives rates
    For OT and Night shift which are the rates.

+ 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 hours
    By Tashia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2014, 02:09 AM
  2. Calculating overtime hours on a timesheet
    By tiffany04530 in forum Excel General
    Replies: 8
    Last Post: 12-01-2012, 04:50 AM
  3. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  4. Calculating Overtime Hours
    By Jonathan78 in forum Excel General
    Replies: 0
    Last Post: 11-02-2009, 05:49 PM
  5. need help w/formula for calculating overtime hours
    By jv749297 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2005, 04:06 PM

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