Solved Formatting does not format every 7th cell in column

October 4, 2014 at 07:10:52
Specs: Windows 7 Enterprise
I am highlighting cells based on dates in an adjacent cell, for an entire column. However no matter how i format the cell, (format painter, or manually enter format), every 7th cell down does not format. Not sure how to upload the speadsheet here though.

Attempting to complete the following...Cell B3 manually enter date, cell C3 have it automatically add 30 days minus weekends and highlight red when date is at or over 30 days old, and in cell D3 I manually add date for completion, which should change cell C3 to green

Cell B13 is a date - No formatting on B column

Cell C3 has the following rules from C3 to C278 for now
(1) =AND(WEEKDAY(D3,2)>1,D3<>"",D3<>"") green format color
(2) =ISERROR(C3) - white format color to hide #NUM!
(3) cell value, less than =NOW() - red format color

Cell D3 is just a date - No formatting on D column

Using Excel 2010

Any help would be appreciated! - Scott

message edited by Tufftoy


See More: Formatting does not format every 7th cell in column

Report •


✔ Best Answer
October 5, 2014 at 13:07:29
For Conditional Formatting to be applied the formula MUST return TRUE,
that is all that is need for CF to work.

SO, the problem with

=AND(WEEKDAY(D3,2)>0

is that it is ALWAYS true
as soon as you put a Date in D3 both parts of the AND statement are TRUE
so your Conditional Formatting will kick in.

IF checking to see what "Day of The Week" it is does not matter, then get rid of it
and simply use the =D3<>""

So you would replace: =AND(WEEKDAY(D3,2)>0,D3<>"")
with the simple: =D3<>""

Also why do you have:

(2) =ISERROR(C3) - white format color to hide #NUM!

What formula is being used to Calculate add 30 days minus weekends
in cell C3?

The only way I can think of to generate a #NUM error,
is if the DATE in B3 is
not really a DATE
but a string of TEXT
that LOOKS like a DATE.

And hiding errors, can be counter productive in many cases.

MIKE

http://www.skeptic.com/



#1
October 4, 2014 at 17:16:43
I believe it's your use of the WEEKDAY() function.

The way it is written, WEEKDAY(D3,2) makes Monday Day 1
so every Monday (every 7 days) your formula fails to be true
because the Day of the Week is NOT Greater then 1, it is 1.

=AND(WEEKDAY(D3,2)>1,D3<>"",D3<>"")

Why do you have two instances of D3 <> ""
one should be sufficient.

MIKE

http://www.skeptic.com/


Report •

#2
October 5, 2014 at 07:28:46
Thank you for the reply, and to be honest, i'm not sure why i have two instances of D3<>""...I'm pretty new at using formulas, and that is just one that i had found on the web, to accomplish what i wanted it's function to do.

Basically what i was trying to do is, in column C, when a date (any date) is entered into D column, have the adjacent cell in column C change green and stay that way. Column B is for when a work order was written, column C is automatically updated to show 30 days, minus weekends, and turn red after that 30 days have passed, and in column D is the completion date of the work order, which when a date is input into a cell should change the adjacent cell in column C to green...sorry if i'm repeating myself!

Do you see another option or fix for the current =AND(WEEKDAY~ formula i have, to where every cell will be true instead of what i have now? I will be happy to send the spreadsheet to you if need be.

Thanks again!


Report •

#3
October 5, 2014 at 08:29:33
For coloring Past Due Dates, see this HOW-TO

http://www.computing.net/howtos/sho...

It should give you some ideas on how to use Conditional Formatting for what you want.

There is also this HOW-TO for Dead Line Dates:

http://www.computing.net/howtos/sho...

Which show how to use Conditional Formatting to highlight cells as a specific date approaches.

See if they help at all.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 5, 2014 at 08:31:56
Great, thank you, I'll will check them out, and let you know.

Report •

#5
October 5, 2014 at 08:46:46
Mike,

I thought about what you said about the 1 standing for Monday. So i revised the formula, and replaced the 1 with a 0, just to see what would happen, and to my surprise it works like I want it to. I also removed the extra D3 <> "", as you said I should be able to only use one.

=AND(WEEKDAY(D3,2)>0,D3<>"")

Thanks again for the replies, and links. Just needed a little motivation to get my mind working I guess.

Thanks again!


Report •

#6
October 5, 2014 at 13:07:29
✔ Best Answer
For Conditional Formatting to be applied the formula MUST return TRUE,
that is all that is need for CF to work.

SO, the problem with

=AND(WEEKDAY(D3,2)>0

is that it is ALWAYS true
as soon as you put a Date in D3 both parts of the AND statement are TRUE
so your Conditional Formatting will kick in.

IF checking to see what "Day of The Week" it is does not matter, then get rid of it
and simply use the =D3<>""

So you would replace: =AND(WEEKDAY(D3,2)>0,D3<>"")
with the simple: =D3<>""

Also why do you have:

(2) =ISERROR(C3) - white format color to hide #NUM!

What formula is being used to Calculate add 30 days minus weekends
in cell C3?

The only way I can think of to generate a #NUM error,
is if the DATE in B3 is
not really a DATE
but a string of TEXT
that LOOKS like a DATE.

And hiding errors, can be counter productive in many cases.

MIKE

http://www.skeptic.com/


Report •

#7
October 5, 2014 at 14:14:36
I changed it to the =D3<> and it worked perfectly, and much simpler!. The date does not matter, as long as when a date is entered in a cell in D column, and the formula works, I'm happy with it.

I can't find the formula in the conditional formatting->manage rules screen, but when I go to the Formulas tab, and select the "Show Formulas" option, it shows up that way for each cell in C column...=WORKDAY(B3-1,30)...yet another that I found while serching the web.

As for the =ISERROR(C3) formula, formatted with white (turns #NUM! white, not cell), I was just looking to hide the #NUM! error so it doesn't clutter up the spreadsheet. It's telling me that there is no date in a particular cell in C column, which I can see, but just didn't want it reminding me for every unpopulated cell. All the columns B,C & D are formatted as dates and nothing else.

Should have come here first for answers!


Report •

#8
October 5, 2014 at 15:45:33
To head off the #NUM error,

replace: =WORKDAY(B3-1,30)

with =IF(B3="","",WORKDAY(B3-1,30))

This way you check the contents of B3 before you try to do a calculation on it.
If B3 is blank, you simply make C3 blank.
If B3 is not blank, then do your WORKDAY calculation.

This will not eliminate the possibility of an error, but if you do get one, you know it has to be the Date in B3 that is the problem. (as I mentioned in my previous post)

Also the WORKDAY(B3-1,30) is actually only adding 29 days to the Date in B3
because the first part is B3 -1 which subtracts one day from the Date in B3,
then it adds 30 days, excluding just Weekends. Holidays are not accounted for.

This should allow you do remove the Conditional Formatting rule: =ISERROR(C3)

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#9
October 6, 2014 at 04:18:18
All changes made, everything works perfect! Thank you so much for the help!

Report •

#10
October 6, 2014 at 09:23:46
Great learining experience for me! Thanks again for the time and effort you put into something that isn't even yours. Everything works as advertised with all the changes you suggested. Much simpler and easier to use. Thanks again!

message edited by Tufftoy


Report •

#11
October 6, 2014 at 10:34:53
Glad I could help.
If you have any new questions, please don't hesitate to ask.

MIKE

http://www.skeptic.com/


Report •


Ask Question