# Having trouble with dates in formulas.

#### olive

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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

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!

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

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.

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

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

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?

Replies
4
Views
230
Replies
2
Views
66
Replies
6
Views
381
Replies
15
Views
875
Replies
4
Views
424

1,219,004
Messages
6,145,698
Members
450,634
Latest member
raphaelfmiranda

### 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.

### Which adblocker are you using?

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

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