Glenn wrote:
Sophia wrote:
I just need a formula, or formulas that will calculate my shift
Differential Time from 8pm-8am. So if you work any hours between
8pm-8am(the next day) those hours apply to shift diff. Also i need
another column that calculates the OT, (over 8 hours) ;and also one
column that calculates any hours worked over 8 hours that are past
8pm; also another that calculates double time (any time worked over 12
hours)
Shift Diff + Hours = 8 hours always

So the header should read:
Start, End, Shift Diff Hours, Hours work, OT hours, Shift Diff OT,
Double Time


Example 1:
So if I worked: 0600 - 2100 on 8-5-08

I would have 2 hours of shift diff, 6 hours of regular, 4 hours of OT,
1 hour of Double Time

(There is no such thing as Shift Diff Double time Any time worked over
12 hours is always DT)

Example 2:
0800 - 2100

8 hours of Regular hours, 4 hours of OT, 1 Hour of Shift Diff OT.
If anyone could help Please Please let me know. i'm not proficient in
Excel to create something like this. Thanks!


I think this is what you are looking for:

A1:H1 - Headers

Start, End, Shift Diff Hours, Hours Worked, OT Hours, Shift Diff OT,
Double Time, Regular

A2 = start time entered as time (6:00)
B2 = end time entered as time (21:00)
C2 = IF(B2<A2,(1+B2-TIME(20,0,0)),MAX(0,B2-TIME(20,0,0)))*24
D2 = IF(B2<A2,(1+B2-A2)*24,(B2-A2)*24)
E2 = MAX(D2-G2-F2-8,0)
F2 = MAX(C2-G2,0)
G2 = IF(D212,D2-12,0)
H2 = D2-G2-F2-E2


This give different results than what you stated in your first example
above, but I believe they are right per your description of what you want.


Regarding formatting, A2:B2 should be formatted as time (h:mm), C2:H2 should be
formatted as numbers per your requirements (I used 0.00).