can anyone help with this

[olive]

HI hope this explains itself better than I did in the past.

Book1.xls
	A	B	C	D	E	F	G	H	I
1	11-October-02	28-Jun-02	condition:dateinb1lessthan5monthsoftodaysdate=green
2		1-May-02	condition:dateinb1=5to6monthspastoftodaysdate=yellow
3		22-Mar-02	condition:dateinb1morethanor=6monthsoftodaysdate=red
4			condition:ifnodatecellstayswhite
5
Sheet1

 

[olive]

Hi thanks for the input. That is almost right but the more than 180days old does not turn red it stays yellow.
See if this makes sense to you:
If the date is between 1-90days old need it to turn green. If between 91-179days old need it to turn yellow. If between 180-730days old need it to turn red. If no date is entered need it to stay clear/white. But need the formula based off of current date so that it will change colors automatically when it hits that range of set dates.
Thank you for your patience!!

 

[bolo]

Hi

Yogi's CF works fine for me. You need to be a bit careful with the absolute references. For me my CF formulae reads

(1) =AND($A$1-$C2>180,$C2<>"")
(2) =AND($A$1-$C2>90,$C2<>"")
(3) =AND($A$1-$C2<=90,$C2<>"")

These formulae were in the CF for the Cell C2. A1 is todays date.

Hope this clears things up

 

[olive]

HI and thanks but the only absolutes I used was for todays date in A1. I tried changing the column to absolute and still only get yellow not red ( even if the date is more than 2 years old). Although the green and yellow and stay clear is working great! :wink:

 

[Yogi Anand]

Hi Olive:

You have change your formating criteria -- so I am going to give you the formulas for the three conditions again. And as I mentioned before, my formulas may have some redundancy, I have made no attemp to alleviate that. But my formulas do work.

I am going to continue to assume that you are going to have =today()in cell A1; and you are going to have another date in cell B1; and the conditional formating is to apply to cell A1. So here goes ...

1. =and(a1-b1>=180,a1-b1<=730,b1<>"") ... Font Color 'Red'
2. =and(a1-b1>90,a1-b1<180,b1<>"") ... Font Color 'Yellow'
3. =and(a1-b1<=90,a1-b1>0,b1<>"") ... font color 'Green

I hope this helps.

Please post back if it works for you ... otherwise post your data clearly identifying your input, your output, formulas you are using, results you got, and results you like to get.

Regards!

Yogi

 

[olive]

Yogi,
Good morning! Sorry I have not stated very good what I was wanting. I want the formating to pertain to the older dates not A1(today)but need it based off of A1. ie if a date is entered in B or C etc... it will turn colors not todays date am working with a large spreed sheet and seeing the colors makes it easier to pick out the dates that are good(green), going bad(yellow) or gone bad(red).
Senetio : If A1 is 12-Oct-02 then cell in B2 with 1-Sep-02 will show green. Cell in C5 with 12-May-02 will show yellow. Cell in F30 with date of 3-Feb-02 will show red.
Have used the cell value is: condition1 >= 37408 condition2 between 37347 & 37407 condition3 between 3700 & 37346 these conditions do what I want but I have to change the numbers everday( not very fun).I know these ranges are not the ones I gave you but they do the trick for that specific range. Have several different ranges that I need to work with so again reason for needing it based off todays date.
If you still do not understand could email you the chart and you can look at it to see exactly what I am talking about. As different columns need different date ranges. I cannot get the conditions or colors to come across the board when I post.
Thank you so much!

 

[stevebausch]

Olive,
{Edited to reflect Olive's feedback}

Noticed this thread keeps getting bumped up, got curious.

This is all about expiration dates.
The differential is to be judged between expiration date, and Today()

You want the background color to change, according to how far 'out' the expiration date is.

When we get the conditionals and attributes sorted out, I will propose a Worksheet_Change event routine. (or, maybe not )

(I am going to make up some psuedo-code here.)
We will define Differential as expiration date minus Today()

Column B threshold is 90 days 'out'

Select Case Column_B_Differential
Case is 60 to 89
(I give up, Olive, you need to sharpen your skils of communication before you talk to 'consultants'. Otherwise, you are just going to run up the bill)



Column C threshold is 180 days 'out'


Column D threshold is 365 days 'out'


Column E threshold is 90 days 'out'


VBA code may not be what many would consider, but once it's parked in the right place, most users won't be able to corrupt it.



_________________
This message was edited by stevebausch on 2002-10-12 17:58
This message was edited by stevebausch on 2002-10-12 18:00

 

[olive]

HI Steve
Exactly right! The expiration dates. Not worried about font would like the background to turn colors stands out more.
Each column has different expiration date range. Example: column B has up to 90days before expiation column C has 180days and column D has 365 Column E is back to 90 days.
Look at last post that is exactly what I want it to do for that specific column but have to change it daily (and it's a real pain). Two posts back is one of the conditions broke down into an example. Once in place would be great if no one could change it. Just need it to be self running off A1( =today() ). I am not sure if this explains it better or not, am not real familiar with the terms.
Thanks in advance!

 

[stevebausch]

Olive,

You will need to study my edited post (above), and supply additional information as to how you want the conditional formatting to behave.

Oh, if you ever wondered why consultants are so expen$ive, it's because the clients are scared to be upfront with what they are trying to accomplish; similiar to Donald Rumsfield conducting a Pentagon briefing.

 

[olive]

Steve
Here goes: Column B may have any date range from present up to 2 year old. if that column has and expiration of 90 days need the cells that are within 60days to go green 61-90 to go yellow 91-730 to go red if no date stay clear. Column C may have date range from present up to 2 years old. need this one to turn green if date is within 150days, yellow if between 151-180days, red if 181-730. Column D date from present to 4 years old, need to go green if before 330 days, go yellow if between 331-365, go red if 1 year -4years old.
Hope this is better explaination if not just call me Excel illiterate!! :wink:

 

[lenze]

I've been off line since yesterday, but you have received several great ideas. Here is what I think will work


Condition 1
=AND(NOT(ISBLANK(B3)),B3<TODAY()-180) Red

Condition 2
=AND(NOT(ISBLANK(B3)),B3<TODAY()-150) Yellow

Condition 3
=AND(NOT(ISBLANK(B3)),B3>TODAY()-150)Green
This message was edited by lenze on 2002-10-12 16:51
This message was edited by lenze on 2002-10-12 16:51