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

  • Thread starter Thread starter bmcaf12345
  • Start date Start date
B

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.
 
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.
 
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.
 
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.
 
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?
 
You're close. try this

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

If this post was helpful to you, please click YES below.
 
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.
 
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!!!
 
Try greater than or equal to

=TODAY()-365

bmcaf12345 said:
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.
Click to expand...
 
Back
Top