Formula Help Needed - Shift Differentials

**mark.k.conroy** · 08-17-2018, 11:55 AM

Hello!

I'm trying to figure out a shift differential formula where I have the following set up:

Column A is the date
Column C is the start time
Column D is the end time

For any time worked after 9pm, there's a differential of $1 over base pay, base pay is cell L5.

So, I want to be able to input a start time of 6pm, and end time of 10pm and have TWO outputs. REG PAY in column H, DIFF PAY in column I, and total pay (which would just be the sum of column H and I).

I'm struggling to get the formulas right.

Additional layer if you can help - on Saturdays and Sundays the ENTIRE shift is considered differential pay.

EXAMPLE that I'm using is 6p-10p on a weekday. I want to see that I have 3 hours x $11 in column H. I want to see I have 1 hour x $12 in column I.
On a weekend if I work 11a-7p, I want to see $0 in column H and 8 hours x $12 in column I.

Thoughts? Prayers? Suggestions? LOL

thanks in advance!

**mark.k.conroy** · 08-17-2018, 11:59 AM

BUDGET.gif

image of how i've got it set up...couldn't figure out how to attach the actual spreadsheet

**modytrane** · 08-17-2018, 01:40 PM

see attached file.

Formula used in G4

HTML Code:

Formula in H4:

HTML Code:

**modytrane** · 08-17-2018, 01:41 PM

Drag the formulas down in columns G and H.

**modytrane** · 08-17-2018, 01:44 PM

Added for condition when shift times are blank.

Update formulas:
G4:

HTML Code:

H4:

HTML Code:

**istank** · 08-17-2018, 01:57 PM

Not sure I uploaded the file right but if I did check it out and see if it meets your needs.

I used the TIME function to provide a 9 pm cutoff and added a differential pay cell. that can be eliminated and just included in the formula.

modytrane's solution is more elegant and streamlined. I like that one better but they both get similar results.