Flagging an outdated cell
 

Along the same lines as another thread I read earlier, I am trying to set up
cells that will turn red or have a validation message pop up if the date in
the cell is over 60 days old from today's date. I want it set up so when I
open the spreadsheet, if the date in, let's say, cell K8 is 7/11/2008, that
the cell should be red, or I have a message pop up when select the cell
saying that this is outdated. Is there a way to do that?

Flagging an outdated cell
 

Select the cells you want to have this feature and note which is the active cell in the selection (that cell will not be shaded in like the other ones are). After selecting your cells, click Format/Conditional Formatting from Excel's menu bar. Select "Formula Is" from the first dropdown. Put this formula in the second field (that appeared when you made the "Formula Is" selection)...

=(TODAY()-E1)60

Next, click the Format button and select the Patterns tab and pick a color you want the cells to be shaded in if they meet your condition. Finally, OK your way back to the worksheet. You can now clear your selection and those cells should now work as you want them to.

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Along the same lines as another thread I read earlier, I am trying to set up
cells that will turn red or have a validation message pop up if the date in
the cell is over 60 days old from today's date. I want it set up so when I
open the spreadsheet, if the date in, let's say, cell K8 is 7/11/2008, that
the cell should be red, or I have a message pop up when select the cell
saying that this is outdated. Is there a way to do that?

Flagging an outdated cell
 

Thank you:). The formula works! But, for some reason TODAY does not work.
However, if put today's date in another cell and refer to that one it works.
What am I doing wrong? And how can use this information to create a
validation for that cell as well?

"Rick Rothstein" wrote:

Select the cells you want to have this feature and note which is the active cell in the selection (that cell will not be shaded in like the other ones are). After selecting your cells, click Format/Conditional Formatting from Excel's menu bar. Select "Formula Is" from the first dropdown. Put this formula in the second field (that appeared when you made the "Formula Is" selection)...

=(TODAY()-E1)60

Next, click the Format button and select the Patterns tab and pick a color you want the cells to be shaded in if they meet your condition. Finally, OK your way back to the worksheet. You can now clear your selection and those cells should now work as you want them to.

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Along the same lines as another thread I read earlier, I am trying to set up
cells that will turn red or have a validation message pop up if the date in
the cell is over 60 days old from today's date. I want it set up so when I
open the spreadsheet, if the date in, let's say, cell K8 is 7/11/2008, that
the cell should be red, or I have a message pop up when select the cell
saying that this is outdated. Is there a way to do that?

Flagging an outdated cell
 

Did you copy the formula exactly as I posted it (the TODAY function needs those empty parentheses after it)? If that is not your problem, you could try the NOW() function (again, with the empty parentheses after it) in place of the TODAY() function... NOW() includes the time of day along with the date, but if all your cells have in them is dates, without times, it should work fine. If your cells included times, the only problem would occur on the 60th day... when the cell would turn color would depend on the time of day in that case. If neither of these solve your problem, then describe to us what "does not work" means... did you get an error, nothing at all happened, something else happened?

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Thank you:). The formula works! But, for some reason TODAY does not work.
However, if put today's date in another cell and refer to that one it works.
What am I doing wrong? And how can use this information to create a
validation for that cell as well?

"Rick Rothstein" wrote:

Select the cells you want to have this feature and note which is the active cell in the selection (that cell will not be shaded in like the other ones are). After selecting your cells, click Format/Conditional Formatting from Excel's menu bar. Select "Formula Is" from the first dropdown. Put this formula in the second field (that appeared when you made the "Formula Is" selection)...

=(TODAY()-E1)60

Next, click the Format button and select the Patterns tab and pick a color you want the cells to be shaded in if they meet your condition. Finally, OK your way back to the worksheet. You can now clear your selection and those cells should now work as you want them to.

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Along the same lines as another thread I read earlier, I am trying to set up
cells that will turn red or have a validation message pop up if the date in
the cell is over 60 days old from today's date. I want it set up so when I
open the spreadsheet, if the date in, let's say, cell K8 is 7/11/2008, that
the cell should be red, or I have a message pop up when select the cell
saying that this is outdated. Is there a way to do that?

Flagging an outdated cell
 

Thank you. The problem was me not putting the empty parentheses after TODAY.
I now have the cell formatted so it will turn red after the 60 day limit, but
is there a way to put that into a validation so a message appears as well?

"Rick Rothstein" wrote:

Did you copy the formula exactly as I posted it (the TODAY function needs those empty parentheses after it)? If that is not your problem, you could try the NOW() function (again, with the empty parentheses after it) in place of the TODAY() function... NOW() includes the time of day along with the date, but if all your cells have in them is dates, without times, it should work fine. If your cells included times, the only problem would occur on the 60th day... when the cell would turn color would depend on the time of day in that case. If neither of these solve your problem, then describe to us what "does not work" means... did you get an error, nothing at all happened, something else happened?

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Thank you:). The formula works! But, for some reason TODAY does not work.
However, if put today's date in another cell and refer to that one it works.
What am I doing wrong? And how can use this information to create a
validation for that cell as well?

"Rick Rothstein" wrote:

Select the cells you want to have this feature and note which is the active cell in the selection (that cell will not be shaded in like the other ones are). After selecting your cells, click Format/Conditional Formatting from Excel's menu bar. Select "Formula Is" from the first dropdown. Put this formula in the second field (that appeared when you made the "Formula Is" selection)...

=(TODAY()-E1)60

Next, click the Format button and select the Patterns tab and pick a color you want the cells to be shaded in if they meet your condition. Finally, OK your way back to the worksheet. You can now clear your selection and those cells should now work as you want them to.

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Along the same lines as another thread I read earlier, I am trying to set up
cells that will turn red or have a validation message pop up if the date in
the cell is over 60 days old from today's date. I want it set up so when I
open the spreadsheet, if the date in, let's say, cell K8 is 7/11/2008, that
the cell should be red, or I have a message pop up when select the cell
saying that this is outdated. Is there a way to do that?

Flagging an outdated cell
 

For future reference, it is always best to copy/paste formulas or VB code that responders post... that way, you should avoid typos and misreads when implementing them.

You can't use Validation because that only reacts to a user's type in. You might be able to do what you want using VB event code, but you will have to define the action you want in more detail to us. Here is the problem with displaying messages as I see it. What if you have 100 (or 1000, or 5000, etc.) cells that are more than 60 days old... how do you want your messages to work? Obviously you won't want 100 (or more) message boxes to pop up (each one requiring the user to click OK), so how do you want them displayed? And what would you want the messages to say? If your cells are in a column, and if the column next to it is unused (you can probably Insert a column to produce this situation), you could use this column to display a message. Let's say Column L is the unused column; then put this formula in the L1 and copy down...

=IF(K1="","",IF((TODAY()-K1)60,"Too old!",""))

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Thank you. The problem was me not putting the empty parentheses after TODAY.
I now have the cell formatted so it will turn red after the 60 day limit, but
is there a way to put that into a validation so a message appears as well?

"Rick Rothstein" wrote:

Did you copy the formula exactly as I posted it (the TODAY function needs those empty parentheses after it)? If that is not your problem, you could try the NOW() function (again, with the empty parentheses after it) in place of the TODAY() function... NOW() includes the time of day along with the date, but if all your cells have in them is dates, without times, it should work fine. If your cells included times, the only problem would occur on the 60th day... when the cell would turn color would depend on the time of day in that case. If neither of these solve your problem, then describe to us what "does not work" means... did you get an error, nothing at all happened, something else happened?

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Thank you:). The formula works! But, for some reason TODAY does not work.
However, if put today's date in another cell and refer to that one it works.
What am I doing wrong? And how can use this information to create a
validation for that cell as well?

"Rick Rothstein" wrote:

Select the cells you want to have this feature and note which is the active cell in the selection (that cell will not be shaded in like the other ones are). After selecting your cells, click Format/Conditional Formatting from Excel's menu bar. Select "Formula Is" from the first dropdown. Put this formula in the second field (that appeared when you made the "Formula Is" selection)...

=(TODAY()-E1)60

Next, click the Format button and select the Patterns tab and pick a color you want the cells to be shaded in if they meet your condition. Finally, OK your way back to the worksheet. You can now clear your selection and those cells should now work as you want them to.

--
Rick (MVP - Excel)


"collyer3" wrote in message ...
Along the same lines as another thread I read earlier, I am trying to set up
cells that will turn red or have a validation message pop up if the date in
the cell is over 60 days old from today's date. I want it set up so when I
open the spreadsheet, if the date in, let's say, cell K8 is 7/11/2008, that
the cell should be red, or I have a message pop up when select the cell
saying that this is outdated. Is there a way to do that?