Conditional formatting

**RAB** · 10-31-2012, 11:36 AM

I have a column of cells representing days of the week running Friday to Thursday. Each cell is time formatted 00:00
How would I do the following Only If each cell contained a time greater than 00:00 between Friday and Thursday change all to red or perhaps another cell somewhere on the sheet.
Eg if each cell F10:F17 were all greater than 00:00 change to red
Or is there another way to complete this

**FDibbins** · 10-31-2012, 11:54 AM

ummm

Please Login or Register  to view this content.

did they invent a new weekday when i wasnt looking lol?

**RAB** · 10-31-2012, 03:05 PM

Thanks for that. The week that the information runs Friday to Thursday the following week

**FDibbins** · 11-01-2012, 12:34 AM

if you are familiar with conditional formatting, that will do it for you with a "new rule" and "use formula" and then use this...
=MAX(F10:F17)>TIME(0,0,0)

if you are not familiar with CF, find it in the home ribbon in styles

**dredwolf** · 11-01-2012, 01:10 AM

if thursday and friday are to be included, is even easier,
Select F10-F17 --> Conditional Formatting--> Highlight Cell Rules-->Greater Than.. enter 0 in the "Cells that are greater than",then hit the "with" drop down button, "custom" if given list doesn't suit your needs

**RAB** · 11-01-2012, 08:43 AM

Thank you very much it does not work the way i intended. I require all the selected cells to change colour only if all the cells selected are greater than zero. If i have seven cells say H10:H17 i would only like all the cells colour to change after the last vacant cell was populated with a time greater than 00:00
So if any cell in the group contained 00:00 nothing would change. The colour change can only happen if the last cell in the group is populated with a time greater than 00:00

**FDibbins** · 11-01-2012, 11:33 AM

ok that is not what you asked in you're 1st post

Please Login or Register  to view this content.

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

**dredwolf** · 11-01-2012, 10:04 PM

that would help immensely!

**dredwolf** · 11-01-2012, 10:32 PM

not saying this is impossible with Conditional Formatting, but you may want to try in the Macros & VBA section, that could come up with a faster and less intensive solution

looks like the basic idea is to break the Column down into 7 day sections, then check to see if all members of the 7 day section are greater than 00:00, if so format with "Red" Fill?
I see the formula to arrive at this solution becoming very, very, LARGE in Excel formulas, not impossible (maybe..) but large!

**RAB** · 11-13-2012, 03:13 PM

Hello sorry been busy. It is a basic shift planner for idiots. One person cannot work seven days in a row without having a day off the next week. So if a time was entered into the planner that had seven days in a row Friday - Thursday the sheet would indicate that he needed a day off the next week.
Also one person cannot exceed 72 hours a week. The problem being that he can exceed recorded hours of more than 72 hours a week if time can be deducted for non productive time to a certain value ie not doing anything for a particular reason weather break downs waiting on equipment etc. This is also calculated on a max daily basis including time taken to travel there etc. Also this planner could include a group off people employed on the same project. I know the Data base idea is the way that this should go but excel is sometimes more acceptable to the majority.
Any ideas would be great if you can think of a better way on an excel sheet