Having trouble with dates in formulas.

[olive]

HI I'm new to this forum so let me start by saying "Forgive Me" if I confuse you.
Here's the problem:
I need to know if there is a way to format a cell that will subtract X#days(say in increments of 30 or more)from current date. Example- if A1 has todays date and B1 has 10-sep-02 and B2 has 1-aug-02 I need a format formula/cell value that will allow an auto color change when the B1(yellow) or B2(red) date is over the limit of days.
Thank you for your help! (in advance of course :wink: )

 

[Yogi Anand]

Hi Olive:

Welcome to the Board!

You can use CONDITIONAL FORMATING for what you are trying to do. Please look at this feature and post back if you need more help.

Regards!

Yogi

 

[olive]

HI Yogi and thank you for the reply.
I tried the conditional format but can't get the equation that I need correect. Right now I have to change the equation every month (very time consuming). I can't seem to find the right formula to base the criteria off of the current date. Any additional help would be appreciated!

 

[PaddyD]

post back with an explicit description of what you're trying to implement...if you've already tried some formulas, post them...

Paddy

 

[olive]

Sorry about that!
ok I have tried formula:
=(b1-today())<30 green
=(b1-today())<90 red
only get green and is for future date not past tried >30 >90 always stays green
=(today()-b1)>=30 green
=(today()-b1)>=90 red
cells stay white

the format that does work is cell value is:
greater than or equal to 37408 green
between 37347 and 37407 red
but this requires updating every month, am working with a very large data base that requires different criteria throughout. I know if I can get just one based off the current date cell that I can figure out the rest.
Just can not figure out the formula or value to use the current date as a base. ie need to turn B1 green if no older than 30 days and turn it red when it reaches 31-60 days.
Hope this explained it better.

 

[PaddyD]

conditional formatting evaluates conditions to true or false. If more than one condition evaluates to true, the format of the first true condition is applied. Therefore:

=(b1-today())<30
=and((b1-today())>=30,(b1-today())<90)

etc

Paddy

 

[Yogi Anand]

Hi Olive:

Considering the case you originally posted, in the worksheet simulation, I have used 2 conditions, and I have shown two cases wherein only one condition is true exclusively.

You have to clarify what exactly are the conditions -- since the way I have shown the conditions, if the first condition is true, the second condition would not even matter.

y021010h1.xls
	A	B	C	D
1	10/11/02	9/10/02		Condition1:today()-B1>30PatternColor:Yellow
2		7/1/02		Condition2:today()-B2>90PatternColor:Red
3
4
5	10/11/02	9/15/02		Condition1:today()-B1>30PatternColor:Yellow
6		7/1/02		Condition2:today()-B2>90PatternColor:Red
Sheet7

</SPAN>

So, please post back if this is what you are looking for -- or else delineate the specifics of your conditions.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-11 00:19

 

[olive]

HI again.
Am trying to figure out how to post the sheet i am working with to give you a better understanding and am having problems. Got it to work once with no formats though. I thought it was the java so I enabled those. That is when I couldn't get the yes anymore. Have tried everything i read on the issue with no luck.
I got the clock fixed so that error is gone. When I click the HTML it works ok up to where I should be getting the "yes".
Now can not get it to post at all, could I please get some help with that first?