I am putting together a workbook where IN / OUT is entered and it calculates Total Hrs and OT Hrs once 40 Hrs has been reached in the week.
Problem:
1) Each "shift" is at least 12 hrs long
2) Some "shifts" span two or more manufacturing shifts
3) Once Hrs is greater than 40 in the week the remaining must be charged as OT -- even if 6 hrs into a shift everything after must be OT and the following days.
4) I am thinking that "IF STATEMENTS" are not the right way to handle this task.
5) The next worksheet will have a database of totals of all the employees to print out for sending to AP each week. This should not be too difficult to do though.
I tried to copy and paste in the "table" but do not think it pastes very well.
Here is a break down of the shift times:
5AM to 5PM -- this covers 3rd, 1st, 2nd shifts
5PM to 5AM -- this covers 2nd and 3rd shifts
Employee John Doe
Scheduled Time Regular Time Over Time
Date In Out Total Hrs Reg Hrs OT 1st Shift 2nd Shift 3rd Shift 1st Shift 2nd Shift 3rd Shift Total
Sunday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Monday 5:00 PM 5:00 AM 12.00 12.00 0.00 0.00 8.00 4.00 0.00 0.00 0.00 12.00
Tuesday 4:45 PM 5:15 AM 12.50 24.50 0.00 0.00 6.25 6.25 0.00 0.00 0.00 12.50
Wednesday 0.00 24.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Thursday 5:00 AM 5:00 PM 12.00 36.50 0.00 8.00 2.00 2.00 0.00 0.00 0.00 12.00
Friday 5:00 AM 5:00 PM 12.00 40.00 8.50 4.00 0.00 2.00 4.00 2.00 0.00 12.00
Saturday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Total 48.50 40.00 8.50 12.00 16.25 14.25 4.00 2.00 0.00 48.50
The following formulas work fine...
Total Hrs formula is: =IF(COUNT(B6:C6)=2,MOD(C6-B6,1)*24,0)
Reg Hrs formula is: =IF((D6=0)*AND(E5=40),0,IF((E5+D6)>40.01,40,E5+D6))
OT Hrs formula is: =IF((E6=40)*AND(D6<>0),(E5+D6)-40,0)
Notice that once Reg Hrs = 40 for Friday that then 8.5 hrs is alloted into OT. And that Hrs in OT shifts needs to populate.
Right now ALL the Reg & OT shift hrs is not populating by formula (only by hand) and I need them to populate by formula.
**What is a streamlined formula to make the last 6 columns before the Total Column populate correctly?**
I am not sure if zip files can be uploaded for you to see the workbook???
Thanks for your help - Steve
Problem:
1) Each "shift" is at least 12 hrs long
2) Some "shifts" span two or more manufacturing shifts
3) Once Hrs is greater than 40 in the week the remaining must be charged as OT -- even if 6 hrs into a shift everything after must be OT and the following days.
4) I am thinking that "IF STATEMENTS" are not the right way to handle this task.
5) The next worksheet will have a database of totals of all the employees to print out for sending to AP each week. This should not be too difficult to do though.
I tried to copy and paste in the "table" but do not think it pastes very well.
Here is a break down of the shift times:
5AM to 5PM -- this covers 3rd, 1st, 2nd shifts
5PM to 5AM -- this covers 2nd and 3rd shifts
Employee John Doe
Scheduled Time Regular Time Over Time
Date In Out Total Hrs Reg Hrs OT 1st Shift 2nd Shift 3rd Shift 1st Shift 2nd Shift 3rd Shift Total
Sunday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Monday 5:00 PM 5:00 AM 12.00 12.00 0.00 0.00 8.00 4.00 0.00 0.00 0.00 12.00
Tuesday 4:45 PM 5:15 AM 12.50 24.50 0.00 0.00 6.25 6.25 0.00 0.00 0.00 12.50
Wednesday 0.00 24.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Thursday 5:00 AM 5:00 PM 12.00 36.50 0.00 8.00 2.00 2.00 0.00 0.00 0.00 12.00
Friday 5:00 AM 5:00 PM 12.00 40.00 8.50 4.00 0.00 2.00 4.00 2.00 0.00 12.00
Saturday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Total 48.50 40.00 8.50 12.00 16.25 14.25 4.00 2.00 0.00 48.50
The following formulas work fine...
Total Hrs formula is: =IF(COUNT(B6:C6)=2,MOD(C6-B6,1)*24,0)
Reg Hrs formula is: =IF((D6=0)*AND(E5=40),0,IF((E5+D6)>40.01,40,E5+D6))
OT Hrs formula is: =IF((E6=40)*AND(D6<>0),(E5+D6)-40,0)
Notice that once Reg Hrs = 40 for Friday that then 8.5 hrs is alloted into OT. And that Hrs in OT shifts needs to populate.
Right now ALL the Reg & OT shift hrs is not populating by formula (only by hand) and I need them to populate by formula.
**What is a streamlined formula to make the last 6 columns before the Total Column populate correctly?**
I am not sure if zip files can be uploaded for you to see the workbook???
Thanks for your help - Steve