Shift Differential TimeSheet Formulas

[Sophia]

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]

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(D2>12,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.

 

[Glenn]

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(D2>12,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).

 

[Sophia]

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.

 

[Glenn]

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.

 

[Lisa Kerr]

Need to get total of hours within start and end times on a timesheet that qualify for shift differential.

I modified C2 to find the total number of hours worked in a shift after 11 PM & it worked!

But...I will also need to find the total number of hours worked between 3 PM and 11 PM on given days.

And the total number of hours worked between midnight and 7 AM...so the inverse of C2 where I needed to find total hours worked after 23:00, I will need the total number of hours worked prior to 7 AM.

Thank you in advance for any feedback! I have been searching for a formula such as C2 for hours.

 

[Shane Devenshire]

Hi,

Suppose the Start Time is in A1 and the End Time in B1 then

=IF(A1>=15/24,IF(B1<=23/24,B1-A1,23/24-A1),IF(B1<=23/24,B1-15/24,23/24-15/24))

for times betwen 3 - 11 PM

=IF(A1>B1,IF(B1<=7/24,B1,7),IF(B1<=7/24,B1-A1,7/24-A1))

 

[James Harris]

I have a similar issue where I need to calculate hours worked between 11pm and 7am for a shift differential. I am not sure how to modify the formula below to make it work. If anyone has a solution or suggection on how to go about this, please let me know.

 

[John]

Hi James
You forgot to post your formula but just for your information;
If your start time is A1 (11:00pm), end time in B1 ( 7:00am, try this,
=MOD(B1-A1,1) format cell> Custom [h]:mm
HTH
Regards
John
in message news:[email protected]...