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!

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.

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).

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" 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.

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.

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.

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
"James Harris" wrote in message ...
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.

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.

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(A1B1,IF(B1<=7/24,B1,7),IF(B1<=7/24,B1-A1,7/24-A1))
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Lisa Kerr" wrote:

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.

This formula =IF(A1=0/24,IF(B1<=4/24,B1-A1,4/24-A1),IF(B1<=4/24,B1-0/24,4/24-0/24))
works GREAT except when the start time is before midnight. If cell A1 (start time) shows 23:00 and B1 (end time) 4:00 the end result is -0.79.
Any ideas how I can get this to work when crossing midnight?

Sure, I can help you with that! Here are the formulas you can use to calculate your shift differential, OT, shift diff OT, and double time:

Shift Diff Hours:

1. =IF(AND(A2=TIME(20,0,0),A2<=TIME(23,59,59)),MIN(B 2-TIME(20,0,0),TIME(8,0,0)),IF(AND(A2=TIME(0,0,0),A 2<TIME(8,0,0)),MIN(B2-A2,TIME(8,0,0)),0))

Explanation: This formula checks if the start time (cell A2) is between 8pm and midnight or between midnight and 8am. If it is, it calculates the shift diff hours as the minimum of the end time (cell B2) minus 8pm or 8 hours. If it's not, it returns 0.

Hours Worked:

1. =B2-A2

Explanation: This formula calculates the total hours worked by subtracting the start time from the end time.

OT Hours:

1. =IF(C28,C2-8,0)

Explanation: This formula checks if the total hours worked (cell C2) is greater than 8. If it is, it calculates the OT hours as the total hours worked minus 8. If it's not, it returns 0.

Shift Diff OT:

1. =IF(AND(A2=TIME(20,0,0),A2<=TIME(23,59,59)),IF(C2 8,MIN(C2-8,B2-TIME(20,0,0)),0),0)

Explanation: This formula checks if the start time is between 8pm and midnight. If it is, it checks if the total hours worked is greater than 8. If it is, it calculates the shift diff OT as the minimum of the total hours worked minus 8 or the end time minus 8pm. If it's not, it returns 0.

Double Time:

1. =IF(C212,C2-12,0)

Explanation: This formula checks if the total hours worked is greater than 12. If it is, it calculates the double time as the total hours worked minus 12. If it's not, it returns 0.

To use these formulas, you can enter the start time in cell A2 and the end time in cell B2. Then, copy and paste the formulas into the corresponding columns. The Shift Diff Hours formula should go in column C, the Hours Worked formula in column D, the OT Hours formula in column E, the Shift Diff OT formula in column F, and the Double Time formula in column G.