Conditional Formating
Posted by Jessica on August 15, 2001 8:40 AM
I have a workdays listed on column "B" and in a another column (column M) I have total hours worked on that workday.
Column B Column M
Sunday 0 hrs
Monday 8 hrs
Right now I have a conditional format that keeps the total hours worked highlighted in yellow until it reaches 8 (8 hours worked per day). My problem is on Weekends I don't want those cells to be subject to the conditional format. How can I have it hightlight cells that are less than 8 hours and are not a Saturday or Sunday? I appreciate any assistance you can give me.
Thanks sooooo much!
Posted by Mark W. on August 15, 2001 8:44 AM
Is Saturday/Sunday a text value (e.g., "Saturday")
or a date value formatted as "dddd"?
Posted by Jessica on August 15, 2001 9:07 AM
It is a date value "dddd", it autofills from a given date.
Posted by Mark W. on August 15, 2001 10:00 AM
Setup a "Formula Is" condition using the formula...
=AND($M2<8,NOT(OR(WEEKDAY($B2,2)=6,WEEKDAY($B2,2)=7)))
Posted by Jessica on August 15, 2001 10:36 AM
It didn't seem to work. I replaced the "2s" with a "10s" since that is what row I am working in, would that create a problem? Here is what I changed: =AND($M2<8,NOT(OR(WEEKDAY($B10,10)=6,WEEKDAY($B10,10)=7)))
This is how it is set up:
Column B Column C Column M
Sunday Aug 19 0 hrs
Monday Aug 20 9 hrs
The weekday in column b calculates from corresponding date in column C.
I really appreciate your help. If this is taking too much of your time, don't worry about it.
Posted by Mark W. on August 15, 2001 11:31 AM
You missed one... "$M2<8"
Posted by Jessica on August 15, 2001 12:36 PM
I'm sorry, it still doesn't work. I guess I should just give up. I'm really sorry for taking so much of your time.
Posted by Mark W. on August 15, 2001 1:15 PM
Don't give up... :0
I didn't notice it before, but your revisions to
my formula -- also changed the 2nd argument to
the WEEKDAY() formula!! It should be 2 -- not 10.
So do this... select all your data rows leaving
the active cell in cell A2 (or 2nd row of the
leftmost visible column) then make these corrections
to your conditional formula.
My fingers are crossed... : ) I'm sorry, it still doesn't work. I guess I should just give up. I'm really sorry for taking so much of your time.
Posted by Mark W. on August 15, 2001 1:18 PM
One more thing!!!
I should have looked more carefully -- you also
messed up the cell reference in column B. Your
conditional formula should look EXACTLY like
this...
AND($M2<8,NOT(OR(WEEKDAY($B2,2)=6,WEEKDAY($B2,2)=7)))
...and follow my instructions below! I didn't notice it before, but your revisions to
Posted by Jessica on August 23, 2001 8:57 AM
Re: One more thing!!!
I'm sorry it took so long to get back to you. I was off a few days on vacation. I tried it again (with your changes) and it worked!!!! Thanks so much, I really appreciate your help and your patience!!! I should have looked more carefully -- you also
