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!


"Glenn" wrote:

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.


Sophia wrote:
It the most part it work! Thanks But the only thing is the Shift Diff column.
It works if i put in anything before 6am, but if i try to put 7am, that
should equal 1 hour of shift diff, but the colomn comes up as 0.


Try this in C2:

=(MAX(0,(TIME(8,0,0)-A2)*24))+(MIN(4,(1-A2)*24))+(MIN(0,-(TIME(8,0,0)-B2)*24))+(IF(B2<A2,MAX(8,B2*24),MAX(-4,-(1-B2)*24)))

Should now count all hours between 8pm and 8am, even at the beginning of the
shift, which doesn't exactly reflect your original request but appears to be
what you want.