# Work Break calculator

#### Rob-UK

##### New Member
Hi gurus !

I would like to create a sheet that auto populates staff breaks in a working day.

I have 2 choices for breaks on an early shift, 2 choices for breaks on a late shift, and 2 choices for breaks on a night shift.

I have a table that shows each person's name and their preferred choices for each shift.

I am hoping that in a new table I can select if its an early, late or night shift, and then using data validation select a list of names that are working. Then a magic button or something that will look at the preferred times for each person working, and populate their break time with one of their choices.
Obviously I do not want everyone to have the same break so need to factor in a max number of times a break can be allocated depending on the day in question!
It sounds complicated to me but hopingnits not, as will save soo much time every day trying to work it all out.

The staff working any one shift change regularly so it's not like I can create a template were I to have the same team every day. There are 25-50 people I need to allocate breaks to each day!!!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Rob-UK

##### New Member
To clarify, each staff member gives their 2 preferred times out of around 6 options. Those 2 options will always be their options hence why a spreadsheet could work I think!

#### Rob-UK

##### New Member
I take it from the unusual lack of responses that this is not at all straight forward, and would require some VB programming etc ?

Equally, if I'm not giving enough information, let me know!

Continued appreciation for any help.

Rob

#### Rob-UK

##### New Member

cells e217 show data. Cells a2 and b2 will be selected by user, and names of those working, and whether dropback shift will be selected from list in cells a4:b18.

Column c4:c18 would show the time selected by the program based on the times available and noting the 2 preferences given.

One additional bit of info will be the maximum number of people on a particular time if that is something I can select. Annoyingly that is likely to change based on the day and how many people are in !!

I will then aim to produce a sheet with the times for each day which this sheet data would populate.

Hope that helps.

Rob

#### Logit

##### Well-known Member
The link provided requires permission to access. Even then, you are only providing an image. Pics are nice but do not provide the actual file to work with.

For best results, create a sample file showing a BEFORE and AFTER sample of what you are attempting to accomplish. Mock up the actual data showing
the before and after.

#### Rob-UK

##### New Member
The link provided requires permission to access. Even then, you are only providing an image. Pics are nice but do not provide the actual file to work with.

For best results, create a sample file showing a BEFORE and AFTER sample of what you are attempting to accomplish. Mock up the actual data showing
the before and after.

Thanks for this. Apologies. I thought the link was an accessible one. Have now put up basic spreadsheet and made accessible to anyone with this link

Thanks.

Replies
2
Views
96
Replies
3
Views
151
Replies
26
Views
3K
Replies
0
Views
74
Replies
3
Views
171

1,129,840
Messages
5,638,666
Members
417,041
Latest member
Molo

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

### Which adblocker are you using?

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

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