Conditional Formatting + Today, Date, Value Functions.

[natheplas]

Hello,


I'm fairly new to Excel and when I was last on here people were able to help me out with the formula i was working on, now I'm seeking further pity with conditional formatting please.


The excel spreadsheet data is generated through a Microsoft query linked to another program so when you hit refresh on excel the data updates.


I have a column that contains dates, a column that contains blank, part or complete and another column that contains worldwide or blank.


I need anything that is before (so the dates gone by) todays date that is not complete, so blank or part to flag in fill red, which i have been able to do.


Using =AND(B1<>"Complete",A1<TODAY()) However it's the rest of it I am struggling with.


I also need anything with todays date (using the today function, not literally today) that is either blank or part to flag orange, but then no fill when 'Complete'.


And then anything from tomorrow + 9 days that is also part or blank to flag green, but again no fill if already Complete.


On top of that, if the row contains the worldwide value on the different column, I would like the whole row to flag yellow, then when complete no fill.


I'm not sure how to have this many conditioned formats at once and for them to all work simultaneously.


So any help with the other formulas or how to do this to generate the results I want is greatly appreciated.


I've got an example spreadsheet of how I would like this to look, but can't see how to upload the attachment, or show you all the containing the first formula I was able to do, but that may need to be changed.


Thank you - Anyone's help with this would be fantastic!


Anything that's ambiguous let me know and I'll try and clear it up - thanks again.


Nathan

 

[JustynaMK]

<today()) however="" it's="" the="" rest="" of="" it="" i="" am="" struggling="" with.
Hello Nathan,

Following your example, where in column A we have dates (e.g. 01/08/2016 - 30/09/2016), in column B {blank, part, complete} and in column C {blank, worldwide}, please do the following:

1. Go to Conditional Formatting > New Rule > Use a Formula to determine which cells to format
2. For the green fill you should have:
=AND($B1<>"Complete";$A1>TODAY();$A1<today()+7)
For the orange fill:
=AND($B1<>"Complete";$A1=TODAY())
...And for the red fill:
=AND($B1<>"Complete";$A1<today())
3. All 3 of them should apply to the specific range, e.g. =$A:$C

I hope it helps.

Now I am not sure how to apply the 4th conditional formatting for:
On top of that, if the row contains the worldwide value on the different column, I would like the whole row to flag yellow, then when complete no fill.
What if column B is blank or contains "part" (meaning the row should be red), but column C equals "worldwide" (meaning the row should be yellow)? Which condition should have the priority?
</today())
</today()+7)
</today())>

 

[JustynaMK]

Apologies, for the red fill you need to enter:
=AND($B1<>"Complete";$A1 < TODAY () )
(not sure why post automatically deleted a part of this formula so I am pasting it with additional spaces)

 

[natheplas]

JustynaMK,

Thank you! I'm out tonight, but first thing tomorrow I will try this out tomorrow and let you know.

The worldwide yellow row fill should supercede all other rules if possible.

They are more important and there for take more precedence.

So if doable I want the other rules in place, bur have the worldwide yellow rule, unless complete to take priority.

Thank you so much!

 

[JustynaMK]

No problem at all Nathan, let me know if it worked.

I think the easiest way to prioritize "Worldwide" condition is to create 4 different Conditional Formatting formulas:

1. Green:
=AND($B1<>"Complete",$A1>TODAY(),$A1<today()+7,$c1<>"Worldwide")
2. Orange:
=AND($B1<>"Complete",$A1=TODAY(),$C1<>"Worldwide")
3. Red:
=AND($B1<>"Complete",$A1<today(),$c1<>"Worldwide")
4. Yellow:
=$C1="Worldwide"</today(),$c1<></today()+7,$c1<>

 

[natheplas]

JustnyaMK,

Magic! Thank you.

I will implement this tomorrow and let you know the results.

Thank you.

Regards,

Nathan

 

[natheplas]

JustnyaMk,

Thanks again for your help so far, but a couple things aren't working and I'm not sure why.

I'd be very grateful if you could help me again please.

Rule red/3 isn't working, when I put the formula in excel it says 'The formula you have typed contains an error'.

Rule green/1 - Is there anyway to change this so that it picks up only the next 9 days coming and not all of the future days. For example, if we used todays date, it would pick up tomorrow 31st-8th Sept and highlight everything between those dates that didn't have complete in it.

Rule 2/orange works perfectly - thank you!

Rule 4/yellow, is it possible that it highlights the entire row if worldwide is in that row, but then if complete is also in that row there is no fill?

Thanks again for your help so far JustnyaMK, I don't want to break any posting rules, but if there's any way to send the small file I'm working on for you to see what I've done so far.

Thanks again

 

[JustynaMK]

Hi natheplas, sure! I will send you my email address via private message. Let's continue the conversation there.

 

[63falcondude]

(not sure why post automatically deleted a part of this formula so I am pasting it with additional spaces)

It has something to do with the HTML code. to avoid this from happening in the future, add a space before and after inequality signs.

 

[natheplas]

Hi JustynaMK, I emailed you earlier. Please let me know if you didn't get it. Thanks.