Having trouble with dates in formulas.

olive

New Member
Joined
Oct 9, 2002
Messages
24
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: )
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

New Member
Joined
Oct 9, 2002
Messages
24
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
post back with an explicit description of what you're trying to implement...if you've already tried some formulas, post them...

Paddy
 

olive

New Member
Joined
Oct 9, 2002
Messages
24

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCD
110/11/029/10/02Condition1:today()-B1>30PatternColor:Yellow
27/1/02Condition2:today()-B2>90PatternColor:Red
3
4
510/11/029/15/02Condition1:today()-B1>30PatternColor:Yellow
67/1/02Condition2: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

New Member
Joined
Oct 9, 2002
Messages
24
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?
 

Forum statistics

Threads
1,144,048
Messages
5,722,224
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top