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 colorCell D3 is just a date - No formatting on D column

Using Excel 2010

Any help would be appreciated! - Scott

message edited by Tufftoy

✔ 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.

IFchecking to see what "Day of The Week" it isdoes 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 weekendsin 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

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

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!

For coloring Past Due Dates, see this HOW-TOhttp://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

Great, thank you, I'll will check them out, and let you know.

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!

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.

IFchecking to see what "Day of The Week" it isdoes 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 weekendsin 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

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!

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

beforeyou 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

message edited by mmcconaghy

All changes made, everything works perfect! Thank you so much for the help!

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

Glad I could help.

If you have any new questions, please don't hesitate to ask.MIKE

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History