Help with converting shift time to hours worked with lunch deductions

F

Frank94

New Member
Joined
Nov 22, 2022
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello everyone!

I am currently a department manager at a retail company. We have about 8 departments with around a total of 100 employee. Currently the way all managers handle scheduling is to enter them in a excel workbook that everyone uses. This has certain drawbacks:
1) Predetermined shifts and lunch break deductions (meaning no custom shift), 2) when having more than 8 employee in the department the workbook hangs so much its easier to print a blank and do it pen and paper method with deducting manually the lunch times. 3) Multiple managers turning in schedules last minute/late since doing them pen and paper.

I use excel to day to day and am comfortable with basic formulas. Now i maybe over my head in this one but im trying to create a easier, less prone to hanging scheduling workbook where everyone can input shifts they can without having to manually calculate the hours worked and being on budget.

The way we calculate a lunch deductions is all depending of the hours scheduled. So someone working from 6:00 to 15:00 has a 9h schedule. We deduct 1h for lunch. so the employee works 8h.

If you work from A: 3 hours to 5h you get no lunch deduction, B: More than 5h but less than 7h, 30 minute lunch C: more than 7h to 10h is 1h lunch and finnaly D: 11h to 12h is 2h lunch deduction.

I was able to convert an inputted shift to hours worked, but when trying to add an IF fonction to read if its a text or input shift (6:00-15:00) and therefore put a value of 0, it keep return as if there was a text present. (this is relating to cell D7)

Below is my new schedule format:

New Schedules - Copy.xlsx
ABCDEFGHIJ
1Delivery
2
3
4Week from 16 octobre au 22 october 2022
5Employe 1Employe 2Employe 3Employe 4
6
7SundayOff12:00-17:00OffOff
80.000.00
9Monday6:00-15:0012:00-17:00
108.005.0013.00
11Tuesday8:00-17:0012:00-19:00
128.007.006.0014.00
13Wednesday9:00-12:006:00-12:00
143.006.009.00
15Thursday
160.00
17Friday
180.00
19Saturday
200.00
210.0019.006.000.000.000.000.0025.00
22
23# of Hour : 25.00hours
24Formation :hours
25Forecast :12095.00
Testing new
Cell Formulas
RangeFormula
D8D8=IF(ISTEXT(D7),0,(RIGHT(D7,5)-LEFT(D7,5)+(--LEFT(D7,5)>--RIGHT(D7,5)))*24)
J8,J20:J21,J18,J16,J14,J10J8=SUM(B8:I8)
D10,C14:D14,D12D10=(RIGHT(D9,5)-LEFT(D9,5)+(--LEFT(D9,5)>--RIGHT(D9,5)))*24
J12J12=SUM(B12,C12,E12,F12,G12,H12,I12)
F21:I21,B21:C21B21=SUM(B8+B10+B12+B14+B16+B18+B20)
D21D21=SUM(E8+E10+E12+E14+E16+E18+E20)
E23E23=J21
E25E25=120
F25F25=E25-E23
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:I7,B9:I9,B11:I11,B13:I13,B15:I15,B17:I17,B19:I19Cell Value="Off"textNO
Cells with Data Validation
CellAllowCriteria
H15:I15List='Data '!$C$2:$C$60
H17:I17List='Data '!$C$2:$C$60
H9:I9List='Data '!$C$2:$C$60
H13:I13List='Data '!$C$2:$C$60
H19:I19List='Data '!$C$2:$C$60
H11:I11List='Data '!$C$2:$C$60
B11List='Data '!$C$2:$C$60
B19List='Data '!$C$2:$C$60
B13List='Data '!$C$2:$C$60
B9List='Data '!$C$2:$C$60
B17List='Data '!$C$2:$C$60
B15List='Data '!$C$2:$C$60
B7List='Data '!$C$2:$C$60
H7:I7List='Data '!$C$2:$C$60


In a perfect world i would love that all the conversion from shift time to hours worked with a lunch deduction be made as manually inputed in employee 1 but i am going for a more realistical 2 colums approach in employee 2 where i can hide colum D so managers dont get confused on why there 2 hours worked columns. For the final workbook There will be a "Data" sheet where Forecast hours and whatever data that needs to be there.

I made a "testing data" sheet where i currently have this table to have a reference to deduct ammount for lunch:

If workshift from to , then subtractlunch
3.005.000.00
5.016.990.50
7.0010.001.00
11.0012.002.00



here is a sample of the original schedule:

New Schedules - Copy.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Delivery
2
3
4Week from 16 octobre au 22 october 2022
5Employe 1Employe 2Employe 3Employe 4
6
7SundayOffOffOffNot Avail.
80.000.000.000.000.000.000.000.00
9Monday6:00 - 15:008:00 - 17:008:00 - 14:00Not Avail.
100.008.008.005.500.000.000.0021.50
11Tuesday6:00 - 15:008:00 - 17:008:00 - 14:00Not Avail.
120.008.008.005.500.000.000.0021.50
13Wednesday6:00 - 15:008:00 - 17:008:00 - 14:00Formation
140.008.008.005.500.000.000.0021.50
15Thursday6:00 - 15:008:00 - 17:008:00 - 14:005:30- 14:00
160.008.008.005.507.500.000.0029.00
17Friday6:00 - 15:008:00 - 17:008:00 - 14:00Off
180.008.008.005.500.000.000.0021.50
19SaturdayOffOffOff7:00 - 12:00
200.000.000.000.005.000.000.005.00
210.0040.0040.0027.5012.500.000.00120.00
22
23Nb. d'heure : 120.00hours
24Formation :hoursHoliday0.006:00 - 16:009.0010:00 - 15:305.507:55 - 17:008.08
25Forecast :1200.00Formation0.008:00 - 18:009.0010:00 - 16:005.50
26Not Avail.0.0011:00 - 21:009.008:00 - 14:005.507:55 - 15:006.08
27Auth. Off0.007:00 - 17:009.0012:00-18:005.50
28Vacations0.0011:00 - 17:005.50
29Off0.007:55 - 16:007.08
3012:00 - 20:007.00
315:30 - 14:308.0013:00 - 21:007.0013:00 - 21:157.32
326:00 - 15:008.0010:00 - 18:007.0017:00 - 21:004.00
336:30 - 15:308.009:00 -17:007.009:00 - 13:004.0017:00 - 21:154.32
347:00 -16:008.008:00 - 16:007.0012:00 - 16:004.00
357:30 - 16:308.0010:00 - 14:004.0010:00 - 14:304.50
368:00 - 17:008.008:00 - 12:004.00
379:00 - 18:008.009:00 - 17:307.5017:30 - 21:003.50
389:30 - 18:308.005:30- 14:007.50
3910:00 - 19:008.0011:30 - 20:007.508:00 -15:006.00
4012:00 - 21:008.007:00 - 13:006.00
4110:00 - 17:006.00
427:00 - 12:005.0014:00 - 21:006.00
438:00 - 13:005.0012:00 - 19:006.00
449:00 - 14:005.00
4510:00 - 15:005.00
4610:30 - 15:305.007:55 - 12:004.08
4712:00 - 17:005.00
4816:00 - 21:005.00
Delivery
Cell Formulas
RangeFormula
A4A4='Data '!A1
B18:H18,B16:H16,B14:H14,B12:H12,B10:H10,B8:H8B8=IF(OR(B7=$K$24,B7=$K$25,B7=$K$26,B7=$K$27,B7=$K$28,B7=$K$29),0,IF(OR(B7=$K$31,B7=$K$32,B7=$K$33,B7=$K$34,B7=$K$35,B7=$K$36,B7=$K$37,B7=$K$38,B7=$K$39,B7=$K$40),8,IF(OR(B7=$N$24,B7=$N$25,B7=$N$26,B7=$N$27),9,IF(OR(B7=$N$30,B7=$N$31,B7=$N$32,B7=$N$33,B7=$N$34),7,IF(OR(B7=$N$37,B7=$N$38,B7=$N$39),7.5,IF(OR(B7=$N$42,B7=$N$43,B7=$N$44,B7=$N$45,B7=$N$46,B7=$N$47,B7=$N$48),5,IF(OR(B7=$Q$24,B7=$Q$25,B7=$Q$26,B7=$Q$27,B7=$Q$28),5.5,IF(OR(B7=$Q$32,B7=$Q$33,B7=$Q$34,B7=$Q$35,B7=$Q$36),4,IF(OR(B7=$Q$39,B7=$Q$40,B7=$Q$41,B7=$Q$42,B7=$Q$43),6,IF(B7=$Q$46,4.08,IF(B7=$T$24,8.08,IF(B7=$T$26,6.08,IF(B7=$T$29,7.08,IF(B7=$T$31,7.32,IF(B7=$T$33,4.32,IF(B7=$T$35,4.5,IF(B7=$T$37,3.5,0)))))))))))))))))
I8,I20:I21,I18,I16,I14,I12,I10I8=SUM(B8:H8)
B20:H20B20=IF(OR(B19=$K$24,B19=$K$25,B19=$K$26,B19=$K$27,B19=$K$28,B19=$K$29),0,IF(OR(B19=$K$31,B19=$K$32,B19=$K$33,B19=$K$34,B19=$K$35,B19=$K$36,B19=$K$37,B19=$K$38,B19=$K$39,B19=$K$40),8,IF(OR(B19=$N$24,B19=$N$25,B19=$N$26,B19=$N$27),9,IF(OR(B19=$N$30,B19=$N$31,B19=$N$32,B19=$N$33,B19=$N$34),7,IF(OR(B19=$N$37,B19=$N$38,B19=$N$39),7.5,IF(OR(B19=$N$42,B19=$N$43,B19=$N$44,B19=$N$45,B19=$N$46,B19=$N$47,B19=$N$48),5,IF(OR(B19=$Q$24,B19=$Q$25,B19=$Q$26,B19=$Q$27,B19=$Q$28),5.5,IF(OR(B19=$Q$32,B19=$Q$33,B19=$Q$34,B19=$Q$35,B19=$Q$36),4,IF(OR(B19=$Q$39,B19=$Q$41,B19=$Q$42,B19=$Q$43),6,IF(B19=$Q$46,4.08,IF(B19=$T$24,8.08,IF(B19=$T$26,6.08,IF(B19=$T$29,7.08,IF(B19=$T$31,7.32,IF(B19=$T$33,4.32,IF(B19=$T$35,4.5,IF(B19=$T$37,3.5,0)))))))))))))))))
B21:H21B21=SUM(B8+B10+B12+B14+B16+B18+B20)
D23D23=I21
D25D25='Data '!A4
E25E25=D25-D23
O33O33=7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H7,B13:H13,B15:H15,B9:H9,B11:H11,B17:H17,B19:H19Cell Value=$K$25textNO
B7:H7,B13:H13,B15:H15,B9:H9,B11:H11,B17:H17,B19:H19Cell Value=$K$29textNO
B7:H7,B13:H13,B15:H15,B9:H9,B11:H11,B17:H17,B19:H19Cell Value=$K$24textNO
B7:H7,B13:H13,B15:H15,B9:H9,B11:H11,B17:H17,B19:H19Cell Valuecontains "21:"textNO
Cells with Data Validation
CellAllowCriteria
B7:H7List='Data '!$C$2:$C$60
B15:H15List='Data '!$C$2:$C$60
B11:H11List='Data '!$C$2:$C$60
B9:H9List='Data '!$C$2:$C$60
B13:H13List='Data '!$C$2:$C$60
B17:H17List='Data '!$C$2:$C$60
B19:H19List='Data '!$C$2:$C$60



Thank you for any help you guys may provide and i apologize in advance for my english as it is not my first language.

Thank you!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
You must log in or register to reply here.

Similar threads

J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
212
Alex Blakenburg
A
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
343
Tetra201
Tetra201
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
1K
felixstraube
F
C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
402
ChetanPuri
C
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
769
esset
esset

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top