excel VBA

[willeng]

Hello guys

I am working on a break schedule for work and I am have a hard time with it I need to take the employee start work time add 2 hours for their first break 4 hours for their lunch and 6 hours for their last break.

also I need it to look to see if there is anyone else on break at that time if so add 15 mins to break time and recheck if not that would be the time the Employee is to go to break

same thing to Lunch time but in stead of 15mins it would 30mins

Thank you for any help anyone can help me with, I have been trying to work this out for over a month

Thanks
Willeng

 

[sergioMabres]

Hi Willeng, so these people work 12 hours a day? That is terrible! How long are breaks? Do workers start a the same time?
Sergio

 

[GR00007]

In Excel you can calculate 2/4/6 hours from start date easily, but how that interacts with actual time in/out and jobs worked is an entirely different question.
Here's an example of calculating 2/4/6 hours from start time:

Start	Break1	Lunch	Break2	B2 Calc	C2 Calc	D2 Calc
8:00	10:00	12:00	14:00	=A2 + (2/24)	=A2 + (4/24)	=A2 + (6/24)

<tbody>
</tbody>

 

[sergioMabres]

Yes got that, to know if they are having a break together I need the spam of the break, how long is the break: 15, 20 minutes, and the lunch break?
This is what I got so far

		W1	15		W2	30		W3	15
Worker	Time	2	1st Brk St	1st Brk End	4	Lunch Brk St	Lunch Brk End	6	Last Brk St	Last Br End	TGTH
1	10:15		12:15	12:30		16:30	17:00		23:00	23:15	1
2	11:15		13:15	13:30		17:30	18:00		0:00	0:15	0
3	10:20		12:20	12:35		16:35	17:05		23:05	23:20	1
4	8:30		10:30	10:45		14:45	15:15		21:15	21:30	0
5	6:40		8:40	8:55		12:55	13:25		19:25	19:40	0
6	4:50		6:50	7:05		11:05	11:35		17:35	17:50	0

<tbody>
</tbody>

The column TGTH will te me to extend the break 15 more minutes

Cheers
Sergio

 

[sergioMabres]

Wait a minute, all entry times are rounded to the Hour? 08:00, 09:00 10:00 that way is so much easier!
Sergio

 

[willeng]

The employees work 8 hours day breaks are 15 mins lunch is 30 mins and the employees start different times Example Emp1 starts at 9am, emp2 starts 10 so on

 

[willeng]

Breaks are 15 mins,lunch is 30 mins
I have some employees that start at the same time and don’t want them to go to break or lunch at the same time.
Example:
Emp1
starts at 9am
first break 11am,
lunch 1pm,
last break 3pm
end shift at 5pm

Emp2
starts at 9am
first break 11:15am,
lunch 1:30pm due to Emp1 at break at 1pm
last break 3:15pm
end shift 5pm

Emp3
starts at 9:30am
first break 11:30,
lunch cant be at 1:30 due to emp2 would be on lunch so emp3 will go at 2pm,
last break 3:30
end shift 5:30pm

This is want I’m looking for excel to do for me

 

[sergioMabres]

Hi Willeng,
Now I get it! 2, 4 and 6 are the number of hours from entering time, good! I am relieved!
Here is what I got, my approach is to find "Shifts" in every time slot, so first worker entering at 09:00 gets slot 0, and second entering 09:00 get slot 1, and so on, see column Shift in time slot, from there on is pretty straightforward.

Breaks

1st

15

2nd

30

3rd

15

Worker

Time Enters

Shift in Time slot

2

1st Brk St

1st Brk End

2

Lunch Brk St

Lunch Brk End

2

Last Brk St

Last Br End

Leave

1

6:00

0

8:00

8:15

10:00

10:30

12:00

12:15

14:00

2

6:30

0

8:30

8:45

10:30

11:00

12:30

12:45

14:30

3

7:00

0

9:00

9:15

11:00

11:30

13:00

13:15

15:00

4

7:30

0

9:30

9:45

11:30

12:00

13:30

13:45

15:30

5

8:00

0

10:00

10:15

12:00

12:30

14:00

14:15

16:00

6

8:30

0

10:30

10:45

12:30

13:00

14:30

14:45

16:30

7

9:00

0

11:00

11:15

13:00

13:30

15:00

15:15

17:00

8

9:30

0

11:30

11:45

13:30

14:00

15:30

15:45

17:30

9

10:00

0

12:00

12:15

14:00

14:30

16:00

16:15

18:00

10

10:30

0

12:30

12:45

14:30

15:00

16:30

16:45

18:30

11

11:00

0

13:00

13:15

15:00

15:30

17:00

17:15

19:00

12

11:30

0

13:30

13:45

15:30

16:00

17:30

17:45

19:30

13

6:00

1

8:15

8:30

10:30

11:00

12:15

12:30

14:00

14

6:30

1

8:45

9:00

11:00

11:30

12:45

13:00

14:30

15

7:00

1

9:15

9:30

11:30

12:00

13:15

13:30

15:00

16

7:30

1

9:45

10:00

12:00

12:30

13:45

14:00

15:30

17

8:00

1

10:15

10:30

12:30

13:00

14:15

14:30

16:00

18

8:30

1

10:45

11:00

13:00

13:30

14:45

15:00

16:30

19

9:00

1

11:15

11:30

13:30

14:00

15:15

15:30

17:00

20

9:30

1

11:45

12:00

14:00

14:30

15:45

16:00

17:30

21

8:00

2

10:30

10:45

13:00

13:30

14:30

14:45

16:00

22

8:30

2

11:00

11:15

13:30

14:00

15:00

15:15

16:30

23

9:00

2

11:30

11:45

14:00

14:30

15:30

15:45

17:00

24

9:30

2

12:00

12:15

14:30

15:00

16:00

16:15

17:30

<tbody>
</tbody>

The formulas are below, first row of data worker 1 is row 3, last row of data worker 24 is row 26

C3 =COUNTIF([COLOR=#5F8CED]$B$2:B3[/COLOR],[COLOR=#EB5E60]B3[/COLOR])-1
E3 =[COLOR=#5F8CED]B3[/COLOR]+([COLOR=#EB5E60]$D$2[/COLOR]/24)+([COLOR=#8D61C2]C3[/COLOR]*[COLOR=#2D9639]$E$1[/COLOR]/(24*60))
F3 =[COLOR=#5F8CED]E3[/COLOR]+([COLOR=#EB5E60]E$1[/COLOR]/(60*24))
H3 =[COLOR=#5F8CED]B3[/COLOR]+(([COLOR=#EB5E60]$D$2[/COLOR]+[COLOR=#8D61C2]$G$2[/COLOR])/24+([COLOR=#2D9639]C3[/COLOR]*[COLOR=#BF4C91]$H$1[/COLOR]/(24*60)))
I3 =[COLOR=#5F8CED]H3[/COLOR]+([COLOR=#EB5E60]H$1[/COLOR]/(60*24))
K3 =[COLOR=#5F8CED]B3[/COLOR]+(([COLOR=#EB5E60]$G$2[/COLOR]+[COLOR=#8D61C2]$J$2[/COLOR]+[COLOR=#2D9639]$D$2[/COLOR])/24+([COLOR=#BF4C91]C3[/COLOR]*[COLOR=#E38222]$K$1[/COLOR]/(24*60)))
L3 =[COLOR=#5F8CED]K3[/COLOR]+([COLOR=#EB5E60]K$1[/COLOR]/(60*24))
M3 =[COLOR=#5F8CED]B3[/COLOR]+(8/24)

I hope this is what you were looking for
Cheers
Sergio