Formula to show a date in red if >/= 365 days old in Excel?

[bmcaf12345]

I keep a log of dates that people receive financial assistance which is in
effect for 365 days. After 365 days they must renew their application.
Currently, I manually look for dates that are 365 days old and change the
text to red. I would like to find a formula that would do this for me.

 

[bmcaf12345]

I looked at conditional formatting and it showed true and false rather than
changing the text color. I am not sure how to do that part. I still want it
to show the date.

 

[bmcaf12345]

I must have been looking at something else for the true & false. Thanks.

In conditional formatting, I put if the cell is greater than or equal to
="today-365", then in the formatting I made the font red. It did not work
but, I am stuggling with how to phrasing it correctly. Thanks in advance for
your help.

 

[Fred Smith]

All functions, including those that have no parameter, need parentheses.
When you want today's date, you must use =Today(). Also, ditch the quotes
(they designate you have text, not a formula). Try:

=today()-365

Regards,
Fred.

 

[bmcaf12345]

I typed my response on here how it showed it in the formula line, excel put
the quotes in there, not me.

I went back in and put the TODAY()-365, like you both said and there was no
change. The date in the cell is 11/9/07, and is over 365 days old so it
should be showing red if the formula is correct. I wonder if I am phrasing it
correctly?

 

[Gord Dibben]

Format>CF>Formula is:

=A1<TODAY()-365


Gord Dibben MS Excel MVP

 

[Barb Reinhardt]

You're close. try this

=today()-365
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[Barb Reinhardt]

Trying again. The first post never showed up. You are quite close, actually.

=TODAY() - 365
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[bmcaf12345]

After reading your post, I went back and looked again at my formula. For some
reason Excel was automatically adding quotes in the middle of my formula. I
didn't understand the relevance or impact of those quotes, and I had left
them alone. Also, I was letting Excel put the = at the beginning of the
formula. So, I went back and typed =Today()-365, hit ok, then opened it back
up again to make sure the formatting stayed exactly as I had typed it and it
did. This time it worked.

I think my problem was 3 things, thinking I needed to say greater than
rather than less than in the beginning. Then I was passively letting Excel
put in quotes and the equals sign in my formula, not realizing how Excel was
interpreting that data.

Now, the formula is working as I intended and I want to thank everyone for
their help!!!

 

[daddylonglegs]

Try greater than or equal to

=TODAY()-365

I must have been looking at something else for the true & false. Thanks.

In conditional formatting, I put if the cell is greater than or equal to
="today-365", then in the formatting I made the font red. It did not work
but, I am stuggling with how to phrasing it correctly. Thanks in advance for
your help.