Program to keep track of work shifts

[owtu]

Hi, I'm working on a program to keep track of work shift based on different inputs. I will have the department in which the work was done, on what shift and day it started, how many hours it took to get done. Depending on the department it will display on a cell the color associated with it. I keep getting stuck in this and need some guidance. I'm trying to avoid VBA, I know this can probably be done with formulas and conditional formatting. Here's a mock up of what I'm trying to do.

For example: if a work was done in QC, it started on 3/4/19 in Day shift and it took 40 hours to get done. Then cells J8 through N8 will be colored red because that's the color of QC. Each cell there is 8 hours, which is why only 5 cells are needed. If the work had been done in 42 hours, then 6 cells should be red because the last 2 hours were worked during grave shift on 3/6/19

Capture1000.JPG

[XLent]

well, you might find it easier (both short & long term) to return a # to the cells in your matrix (I6 onwards)

the above means your CF rule can be based on the number in the cell, this will also future proof against calculations you may choose to do latterly based on "colour".

with the above in mind... based on your screenshot (files are much better as people don't have to guess at cell references / contents, particular when using merged cells)

Please Login or Register  to view this content.

you should then find you have 0s, 1s, 2s & 3s... where 0 is "nothing", 1 is Engineering activity, 2 is PM activity and 3 is QC activity.

if you wish to, apply a custom number format to this range of ;;;@ and the numbers (schedule key) will disappear from view (they're still there, obviously)

then apply your CF rule based on cell value = 1, 2, 3 etc...

edit: for ease of reference I've added a working example with the above in place...

[owtu]

Thank you XLent. It works but whenever I click on the formula for any cell it stops working and instead of displaying the color it just display the formula as if it were a comment. What's causing that?

[XLent]

Sorry, I confess I don't quite understand... if you can, post an image of the behaviour.

[Melvosh]

formulas showing.PNG

I believe this is what owtu is referring to. If you edit one of the formula cells, then hit enter, the formula shows up rather than the conditional formatting. I think this has to do with the custom cell format. The only solution I can think of is to change the format back to general, and set the font color the same as the fill color in the conditional formatting so the numbers remain "hidden".

[owtu]

Yes, Melvosh. That's precisely what I mean. I tried changing the format to general and it didn't work either.

[Melvosh]

Try the attached file. If it works, please keep in mind that XLent did all the hard work!

[owtu]

Thank you so much XLent, it works! And thank you Melvosh for finding the bug. Why was it doing that? What did you change?

[Melvosh]

And thank you Melvosh for finding the bug. Why was it doing that? What did you change?

I actually just changed the cell format to General, and changed the conditional formatting to change the font color to the same color as the fill color. I think the problem was the custom cell format, but I'm not 100% sure.