Results 1 to 9 of 9

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

Threaded View

  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

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