how can I create a Date alert in excel 2003?

Question

In other words, I have an excel worksheet that I need to keep track of dates in two columbs. What I would like to do is have an alert to let me know that a date has become a year old.
I am sorry that I didn't clairify my question. PLZ help.

Answer 1

If your goal is to see some kind of visual alert when a date is more than a year old (from Today's date), then you need to apply a simple calculation to the date to determine if it was more than 365 days ago. You'll need some background:

In Excel a date is really just a number that represents the number of days since Jan 1, 1900. Excel applies a special format to the cell so that you cannot tell that it is really just a number. The point is that if you want to know the number of days between two dates, you literally just subtract them. So for example, if I have today's date (3/30/07) in cell A1, and I have 1/1/2007 in cell B1, then if I go to cell C1 and type in the formula:
=A1-B1

then you will see a strange but meaningful result of "3/28/1900". What you have to realize is that Excel is making a mistake by formating the number of days that passed (88) as a date. Someone could just fix that by going to Format > Cells and changing the format from "Date" to "General".

But let's say you have dates in column A and you want to see a warning in column B if any of them are more than a year ago. You might use an IF THEN function in column B, in conjunction with the NOW() function, which always returns today's date according to the computer's system clock. Your formula in column B might be:
=IF(NOW()-A1>365,"Yes","No")

Then copy the formula down, and if the dates in column A are in the past, then the numbers they represent will always be lesser than today's date. Eventually some may be 365 or more different that today's date. When that happens then instead of seeing the word "No" written to the right you will see the word "Yes".

A fancier and smoother way of doing this is to use conditional formatting. What you are doing is telling Excel to change the appearance of your date cells (in column A) so that you can easily see if one of them is a year ago. You won't need any column B, instead just highlight the dates in column A and on the menu go to Format > Conditional Formatting. Then set your Condition 1 to a formula and (presuming the first date in your range of dates is in cell A1) type the formula =NOW()-A1>365. Then press the Format button and pick an unusual font color, like red.

Then if any of them are a year ago, they will become red.

Answer 2

Having a pop-up reminder is very distracting. You can use conditional format to highlight dates that need users attention.