+ Reply to Thread
Results 1 to 9 of 9

FORMULA NEEDED: Shift Differential based on Day of week and hours...

  1. #1
    Registered User
    Join Date
    08-16-2017
    Location
    Madison, WI
    MS-Off Ver
    2011
    Posts
    20

    FORMULA NEEDED: Shift Differential based on Day of week and hours...

    I'm trying to come up with a spreadsheet where I can enter a schedule and it will compute estimated earnings. The job has a shift differential so I need to computer the regular hours and the shift differential. If hours are worked after 7pm M-F OR any hours on a Sat/Sun I want to be able to compute those as Differential hours. I currently have a cells that deliniate:

    Day of Week (column A)
    Start Time (column C)
    End Time (column E)

    I have the formula for figuring out the differential hours, but I'm struggling with computering the IF/THEN statment to tell excel to NOT computer regular hours on a Saturday or Sunday. Based on the issue I seem to be having, my formulas are not taking into consideration the day of the week for Sat/Sun and skipping that part of my formula for some reason and simply doing the "false" part of my if / then formula. PLEASE HELP!

    For my regular hours formula I have: =IF(OR(A3="Sun", A3="Sat"),"0",(E3<C3)*"19:00"+MIN(E3,"19:00")-MIN(C3,"19:00"))
    For my differential hours formula I have:=IF(OR(A3="Sat",A3="Sun"),E3-C3,(E3<C3)*("23:30"-"19:00")+MEDIAN(E3,"19:00","23:30")-MEDIAN(C3,"19:00","23:30"))
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-16-2017
    Location
    Madison, WI
    MS-Off Ver
    2011
    Posts
    20

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    Sorry, I should mention as well that there is no option for working past 11:30pm or before 6:00am

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    mark.k.conroy welcome to the forum.

    Please edit your profile to indicate what version(s) of Excel solutions are to be applied. Members often propose solutions with this in mind.

    If you don't know how to determine this Click on File and then Account. The version (Example: Excel 2013) will be on that page.

    If this doesn't work your version is likely an older one. Consult the help file.
    Dave

  4. #4
    Registered User
    Join Date
    08-14-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2013
    Posts
    8

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

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

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    Or try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The "days of the week" you are referencing as text are actually numbers (dates are numbers) formatted as weekdays. The formatting is cosmetic only. It does not affect the value or data type.

  6. #6
    Registered User
    Join Date
    08-16-2017
    Location
    Madison, WI
    MS-Off Ver
    2011
    Posts
    20

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    Awesome, thank you! So that worked for my regular hours PERFECTLY! Now I'm struggling to get my differential hours to count after 7pm M-F and all day Sat/Sun! Suggestions?

  7. #7
    Registered User
    Join Date
    08-16-2017
    Location
    Madison, WI
    MS-Off Ver
    2011
    Posts
    20

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    THANKS! I added the year - Excel 2011 (for a mac too lol)

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    RE: Post #6

    Does this do it?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    RE: Post #7. Thank you for updating your profile including the Mac part. It helps.

  9. #9
    Registered User
    Join Date
    08-16-2017
    Location
    Madison, WI
    MS-Off Ver
    2011
    Posts
    20

    Re: FORMULA NEEDED: Shift Differential based on Day of week and hours...

    You, my friend ARE AMAZING!!

    Would it be too much to want to also deduct out 30 minutes from the differential amounts if the shift is 6 hours or more!? LOL

+ 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. Shift Pattern Adding Hours based on Shift etc
    By fgbuk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2016, 07:56 PM
  2. Replies: 1
    Last Post: 06-10-2016, 11:38 PM
  3. [SOLVED] Formula needed to total daily work hours and hours per week
    By amkampbell in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-24-2016, 07:33 PM
  4. Calculating shift hours above 40 per week
    By evansmike881 in forum Excel General
    Replies: 2
    Last Post: 05-02-2015, 05:54 AM
  5. Replies: 3
    Last Post: 07-21-2013, 05:45 PM
  6. identify differential hours in a shift that goes over midnight
    By CanYouDoThis? in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2012, 03:20 PM
  7. shift differential
    By flyeaglesfly in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2008, 04:09 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