Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Formatting Conundrum
Hi all.
I'm having something of a conundrum with conditional formatting. Basically i've got a spreadsheet with training qualifications and their expiry dates listed on it. I've got it working so that the cells with the dates are highlighted red when todays date is within 30 days of the expiry date: Formula Is =TODAY()=F5-30 I have also set up Formula Is =TODAY()=F5+31 which triggers a blank highlight because I found the cell turned red when no value was present in it. Note: It's only purpose is to keep the cell white until Condition 2 is met. This all works perfectly fine. My problem is that my superior has asked me to add in a second condition between these two that highlights the expiry date cell orange when the TODAY() value is in the 60 to 31 days period before the expiry date. I've tried using the Cell Value Is Between condition but to no avail. Formula I tried was: Cell Value Is Between =TODAY()=F5-60 AND =TODAY()=F5-31 As far as I can guess, it's not Condition 1 (the blank formula) that's causing a problem, as that is just set to + rather than - from F5. So I think it's just the fact that I've got the Cell Value Is Between condition written wrong. Any clues or even answers to this would be greatly appreciated. Cheers, Suedonym |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Conundrum
Suedonym laid this down on his screen :
Hi all. I'm having something of a conundrum with conditional formatting. Basically i've got a spreadsheet with training qualifications and their expiry dates listed on it. I've got it working so that the cells with the dates are highlighted red when todays date is within 30 days of the expiry date: Formula Is =TODAY()=F5-30 I have also set up Formula Is =TODAY()=F5+31 which triggers a blank highlight because I found the cell turned red when no value was present in it. Note: It's only purpose is to keep the cell white until Condition 2 is met. This all works perfectly fine. My problem is that my superior has asked me to add in a second condition between these two that highlights the expiry date cell orange when the TODAY() value is in the 60 to 31 days period before the expiry date. I've tried using the Cell Value Is Between condition but to no avail. Formula I tried was: Cell Value Is Between =TODAY()=F5-60 AND =TODAY()=F5-31 As far as I can guess, it's not Condition 1 (the blank formula) that's causing a problem, as that is just set to + rather than - from F5. So I think it's just the fact that I've got the Cell Value Is Between condition written wrong. Any clues or even answers to this would be greatly appreciated. Cheers, Suedonym Use the AND() function so you only have 2 format conditions. Place the least likely condition ahead of the most likely... Condition1: Formula Is: =AND(F5<"",(F5-31)TODAY(),(F5-60)<TODAY()) Condition1: Formula Is: =AND(F5<"",(F5-31)<TODAY()) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
|
|||
|
|||
Thanks for the reply Garry, and sorry I didn't say thank you sooner as i've been on holiday. That worked nicely thank you.
Suedonym |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Conundrum
Suedonym laid this down on his screen :
Thanks for the reply Garry, and sorry I didn't say thank you sooner as i've been on holiday. That worked nicely thank you. Suedonym You're welcome! ..feedback is always appreciated! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ListBox Conundrum | Excel Programming | |||
Conundrum | Excel Discussion (Misc queries) | |||
VBA conundrum | New Users to Excel | |||
CheckBox Conundrum | Excel Programming | |||
ISERROR Conundrum | Excel Worksheet Functions |