time sheet and majority hours

[Amanda]

I am not even sure if this is possible, but I figured I would ask the experts.

I would like a tool for my managers to use to assist them in paying their
employees correctly, however we have shift differential rules that make it
confusing.

The sheet would have a Time in and Time Out column (let's say A & B)
The sheet would then need to calculate the total length of the shift (Column
C)

Now for the tricky part...
Based on the length of the shift - a certain fixed number determines the
majority of the shift -
if the shift is at least 6 hours, but less than 8, then 4 hours
If at least 8 hours, but less than 10, then 5 hours

Then with that "majority number in mind", they would have to work that many
hours after 1500 (3 o'clock) - so we know to may shift differential for the
entire shift

For example

0900-1800 - Total time is 9 hours (so 5 determines the majority). Since the
employee did not work 5 hours from 1500, then the entire pay will be 1st shift

1100-1800 - Total time is 7 hours ( so 4 hours determines the majority).
Since the employee worked 4 hours in 2nd shift (after 1500), then the entire
shift will be paid 2nd

Like I said, this may not even be possible, but I figured it would be worth
a shot!

Thanks ahead of time

 

[Sean Timmons]

What if less than 6 or more than 10?

=if(end - start <8,if(end - 4/24 >= 15/24,2nd shift pay,1st shift
pay),if(end - 5/24 >= 15/24, 2ndshift pay, 1st shift pay)

should get the below criteria. Add more if other hour for shift...

 

[Sean Timmons]

What if less than 6 or more than 10?

=if(end - start <8,if(end - 4/24 >= 15/24,2nd shift pay,1st shift
pay),if(end - 5/24 >= 15/24, 2ndshift pay, 1st shift pay)

should get the below criteria. Add more if other hour for shift...

 

[JBeaucaire]

Set it up like so:

A2 = 3:00 PM (time 2nd shift starts)

(start)
A4: 11:00 AM

(end)
B4: 8:00 PM

(hours, format as General)
C4: =(B4-A4)*24

(majority)
D4: IF(AND(C4>=6,C4<8),4, IF(AND(C4>=8,C4<10),5,0))

(hours into 2nd shift)
E4: =(B4-$A$2)*24

(majority selection 1st or 2nd)
F4: =IF(E4>=D4,"2nd","1st")

Now, that was just to show you all the logic. In G4, we can merge all those
mini-formulas into one megaformula, hard to read, but does that work in a
single cell:

G4: =IF((B4-$A$2)*24>=IF(AND((B4-A4)*24>=6,(B4-A4)*24<8), 4,
IF(AND((B4-A4)*24>=8,(B4-A4)*24<10), 5, 0)), "2nd", "1st")

Now that you have the shift choice, do your math on the pay rate.

 

[JBeaucaire]

Set it up like so:

A2 = 3:00 PM (time 2nd shift starts)

(start)
A4: 11:00 AM

(end)
B4: 8:00 PM

(hours, format as General)
C4: =(B4-A4)*24

(majority)
D4: IF(AND(C4>=6,C4<8),4, IF(AND(C4>=8,C4<10),5,0))

(hours into 2nd shift)
E4: =(B4-$A$2)*24

(majority selection 1st or 2nd)
F4: =IF(E4>=D4,"2nd","1st")

Now, that was just to show you all the logic. In G4, we can merge all those
mini-formulas into one megaformula, hard to read, but does that work in a
single cell:

G4: =IF((B4-$A$2)*24>=IF(AND((B4-A4)*24>=6,(B4-A4)*24<8), 4,
IF(AND((B4-A4)*24>=8,(B4-A4)*24<10), 5, 0)), "2nd", "1st")

Now that you have the shift choice, do your math on the pay rate.

 

[Amanda]

Wow...that is great...I like that you explained it all out to me as well....I
didn't think it would work...but since you got that far....can I try to throw
in one more twist?

We actually have 3 shifts....that the majority rules apply....

2nd shift is 3:00 pm to Midnight
3rd shift is 11:00 pm to 08:00 am

Is that possible to add?

Also, is there a way to input time in military time, without having to write
the AM and PM....like rather than 2:00 pm, I can input 1400, but it knows
that is time?


Thanks a ton!!!

 

[Amanda]

Wow...that is great...I like that you explained it all out to me as well....I
didn't think it would work...but since you got that far....can I try to throw
in one more twist?

We actually have 3 shifts....that the majority rules apply....

2nd shift is 3:00 pm to Midnight
3rd shift is 11:00 pm to 08:00 am

Is that possible to add?

Also, is there a way to input time in military time, without having to write
the AM and PM....like rather than 2:00 pm, I can input 1400, but it knows
that is time?


Thanks a ton!!!

 

[JBeaucaire]

Sorry, I didn't notice the reply. Honestly, I'm drawing a blank for the
moment on making this s a 3-way logic-test.

But the Military time this is easy. Click on some blank cell and just enter
this:

20:00

excel now knows that's a military format time, highlight the cell and click
on the Format Painter, then highlight all your existing Time Cell entries and
they will all display in military format.

Excel keeps time with hidden decimal values, so you changing the DISPLAY
parameters has no effect on the formulas used to manipulate the time values.

Anytime you want to enter in military time, just type in the semicolon, too.
Enter 21: and press ENTER and it will register as time. For 9:30 PM, just
enter 21:3

Hope that helps.

 

[JBeaucaire]

Sorry, I didn't notice the reply. Honestly, I'm drawing a blank for the
moment on making this s a 3-way logic-test.

But the Military time this is easy. Click on some blank cell and just enter
this:

20:00

excel now knows that's a military format time, highlight the cell and click
on the Format Painter, then highlight all your existing Time Cell entries and
they will all display in military format.

Excel keeps time with hidden decimal values, so you changing the DISPLAY
parameters has no effect on the formulas used to manipulate the time values.

Anytime you want to enter in military time, just type in the semicolon, too.
Enter 21: and press ENTER and it will register as time. For 9:30 PM, just
enter 21:3

Hope that helps.