# Thread: Date formula problem (Excel 2000)

1. ## Date formula problem (Excel 2000)

Hi again folks, have one here that I'm stumped on. If I want to take Column A and create a conditional format on that column to show me if any date typed in that column is exactly one year earlier then the current date, (by turning the font red or something similar), what expression would I type into the conditional format "Formula Is" command line? I sure would appreciate any help on this you can give me.
Thanks so very much,
As Always,

2. ## Re: Date formula problem (Excel 2000)

I may have this backwards, but you'll get the drift. Where C4 contains the old date and C5 the 'new' date and the conditional formatting:

=DATE(YEAR(C5)-1,MONTH(C5),DAY(C5))=C4

3. ## Re: Date formula problem (Excel 2000)

Try this

=int(A1)=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

Steve

4. ## Re: Date formula problem (Excel 2000)

I have tried this formula, and I have tried the next post formula, and neither is giving me anything close to what I'm asking for I'm afraid. If I highlight column A, choose Format conditional formatting, I need to put a formula into the first conditional box that states this: If a user types any date into column A that is anything older then one year from the current date then I want the font in the cell to turn red. Does that make sense? Like this: If DATE typed in cell is < TODAY(), then turn cell red
Thanks, and appreciate any feedback.

5. ## Re: Date formula problem (Excel 2000)

Try this:
In Conditional formatting, pick "Formula is" and enter the following:
<pre>=DATEDIF(A1,NOW(),"M")>=12</pre>

where A1 is the address of the first cell you're using, then specify the Format to have red font color.
Then copy this cell down the A column. The A1 address is relative, so always refers to the current cell (ie in cell A2 the formula will contain A2 etc)

Have fun!
Ian.

6. ## Re: Date formula problem (Excel 2000)

Bless you!
That was it, and works like a charm...I salute you! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

7. ## Re: Date formula problem (Excel 2000)

I'm sorry, in your original post you stated "exactly one year after", and I took it literally meaning one year to the day, neither more nor less, (and I think Steve did also, but his interpretation was exactly one year to the day before 'today'), rather than a year or more older. C'est la vie! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

8. ## Re: Date formula problem (Excel 2000)

Yes, I agree with John's statement. I gave you what you asked for, it might NOT have been what you WANTED, but it was what you asked:
"I want to take Column A and create a conditional format on that column to show me if any date typed in that column is exactly one year earlier then the current date"

=int(A1)=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

will do that. If today is 9/13/2002, and you had the above conditionally formatting set. When you would have 9/13/2001, that date condition would be TRUE. Tomorrow on 9/14/2002, it would be FALSE.

=int(A1)<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
would be true for any day OLDER than 1 year from today.

You also could use:
=DATEDIF(A1,NOW(),"y")>=1

or even:
=now()-A1 > 365
(I wouldn't worry about leap years if I only cared about "more than a year")

As John put it, C'est la vie!
Steve

9. ## Re: Date formula problem (Excel 2000)

You are absolutely right. I am sorry. I did say "exactly", and I shouldn't have. Thanks for hanging in there with me as always, and do appreciate all the feedback, and all the suggestions. You outdo yourselves.