date and cell criteria dependent conditional formatting

[UKMillionaire]

Excel Problem Snip.jpg Please take a look at the attached queries. Any help very much appreciated!

[ragulduy]

So in E4, this would return true for accepted but no notification sent:
=and(C4="Accepted",E4="")
For F4:
=and(F4="",TODAY()>=E4+5)
And D4:
=and(C4="Pending",D4>=TODAY()-7)

[UKMillionaire]

Thanks for a quick response Yudlugar.
So am I putting the above in as a formula into the cell or do I need to do this via the conditional formatting tab?

[ragulduy]

That's what goes in your conditional formatting formula. (i.e. you want it to return true so it applies the conditional format when your criteria are met).

[UKMillionaire]

In fact I plugged in your 3 formulas and it mentioned about circular references. Anyway I carried on and entered a date into Estimated Project date and it gave the following

Quote Issued (Date) Status Estimated Project Start (Date) Notification Sent (Date) Notification Acknowledged (Reference/Date/Contact)
02/01/2013 Pending 12/01/2014 0 00-Jan-00

[ragulduy]

Can you upload your workbook please and I'll add the conditional formats in.

[UKMillionaire]

Hang on a few minutes. I'm entering these and things are looking promising. But just need to test a few colours/dates to see everything works good.

[UKMillionaire]

OK Yudlugar I've run some tests by playing around with some new dates as the formulas are relevant to today's date. I think the following is occurring but accept some user error on my part. I dont know much about the code but on examination it appears you are essentially separating a false/positive condition by a comma and that triggers the formatting outcome;

For E4: Notification Sent Cell
=and(C4="Accepted",E4="") - This seems to do as required and answers point 2 of my queries.

For F4: Notification Acknowledged Cell
=and(F4="",TODAY()>=E4+5) - For cell F4 I would be expecting that to fill red after the expiry of 5 days from the date of the notification sent date which it does. I tested a date of today (it had no fill) and a date of over 5 days ago (and it filled red). And when I enter text the red fill disappears which is good - not sure the formula was telling it to do that but it does so no complaints. Although when the job is still Pending status it still fills red and I would prefer if it didn't. The only time the notification acknowledged cell fills red is if it is empty and 5 days has elapsed since sending notification otherwise is should have no fill. This is Point 3 of my queries.

And D4: Estimated Project Start Date
=and(C4="Pending",D4>=TODAY()-7) - When status is pending and we are within 7 days of todays date the cell fills orange when we are more than 7 days the cell remains no fill and this is what I wanted. However as per previous cell F4 remains filled red when it shouldn't be? Once I switch the job to accepted the orange disappears and instead the red fill highlights the notification sent cell which is good and the red fill on the acknowledgment disappears although not sure why it is there in the first place? This is point 4 of my queries.

Do you work freelance, as the above is a snip of my problems. I have a huge spreadsheet which I could do with some assistance on and from that I want to do mail merging to word. I will of course pay but despite my username Im not rich at all!
thanks

[ragulduy]

Hi - yes, the =AND() function allows you to enter several conditions separated by commas. If all conditions are true, the =and() function evaluates to true, if one or more conditions is false the = and() function evaluates to false.

So for F4, TODAY() returns todays date, so today()>=E4+5 is saying that if todays date is more than 5 days after the date in E4, to return to true, and also that the cell F4 itself is blank. You could add in an additional comment to state that it needs to be accepted like this:
=and(C4="Accepted",F4="",TODAY()>=E4+5).

I have sent you a PM regarding freelance work. Note - this forum has a commercial services section which you may wish to look into.