Employee scheduler

**Abdel** · 05-17-2009, 10:25 PM

- Trying to put together an employee scheduling workbook containing multi worksheets
- Issue : how can I color a range of cells in 1 worksheet based on (time in /time out) from another
example
Sheet 1 : cell A= employee names B=time in(5am) C =Time out(4pm)
sheet 2 : cell A1 = employee name.Would like formula to show Yellow color from 5am to 930am(cells b1:h1), green from 930am to 4pm(cells I1:v1)

**DonkeyOte** · 05-18-2009, 03:26 AM

Welcome to the Board.

I would suggest you upload a sample file with Dummy data - this will make it easier for people to provide a solution given they will be using your exact layout etc... to attach a file simple post a reply and use the paperclip icon and follow instructions (if you don't see the icon when posting a reply click the "Go Advanced" button)

**Abdel** · 05-18-2009, 09:22 PM

Here is a sample of the scheduler I am trying to put together :scheduler.xls

**teylyn** · 05-18-2009, 09:39 PM

Hi,

first, you should get rid of the merged cells on sheet 2. They can lead to all kinds of trouble. Use the horizontal alignment option "Center acoss selection" on the Alignment tab of the formatting box instead.

What is the rule behind how many hours should be yellow, how many green, how many blue? This does not come from your data, as far as I can see. You'd need to give Excel something to work with.

Oh, and the formatting for the break time is using borders. These can not be set with conditional formatting, but the background colour should be fine, as long as there are only three conditions.

So, can you give us a bit more info?

**Abdel** · 05-19-2009, 11:16 PM

I made some changes to better explain what I need; eliminated blue color,eliminated bordering for breaks.
TY all for your help.

**DonkeyOte** · 05-20-2009, 03:02 AM

See attached, based on which I would make a few points:

1. You should bring across start/end times from Sheet1 so as to avoid conducting repetitive calculations on Sheet2 (see AO/AP on Sheet2)

2. You should use a 24 scale in your headers (simplifies things for you see Sheet2!B1:AM1)

3. By setting a value in the Matrix itself (G/Y) you could simplify your conditional formulae (you could set the conditional formats to use a text colour = fill colour such that the text is not visible) .. you could do the calcs in the conditional formulae direct but IMO it is easier to audit/adapt when the formulae are in cells directly ... a case of personal preference.

4. There was some discrepancy in your setup re: shifts (9pm or 9.30pm finish?)- if the shifts are inconsistent things will get a lot more complex very quickly.

Regardless, I hope this helps a little.

**Abdel** · 05-21-2009, 11:50 AM

So far so good...TY
-Is there a way to keep regular time format (nonmilitary)?
-Is there a formula to hide the name of all employees who are not scheduled ? Ie, IF employee 4 does not have any hours scheduled in sheet1, his name will not appear on the cell (a5)in sheet 2
- Another problem I am having is that whenever I use the space bar to delete the data I enter in sheet1 , it arases the formula? IE : enter corrections of time start or ends...?

TY Again