Prev: Formula to breakdown original amount with two components to the pe
Next: Data validation
From: hadeeter on 5 May 2010 16:15
I'll do my best to explain my question, please let me know if more
information is needed.

I have a "calendar" of sorts that is in Excel format. It lists several
events, dates that the events will take place, and other pertinent
information. I have been asked to format the calendar in such a way that when
I enter in a new event, it will be in red text for 30 days, and then convert
to "normal" or black text after being in the spreadsheet for 31 days.

Is there a way to set this up with Conditional Formatting? Everything I have
found relates to the date actually typed into the spreadsheet. I need it to
be from the date the data is entered (regardless of the date typed in the
field).

Thanks in advance for any help.
From: Squeaky on 6 May 2010 15:16
Hi Hadeeter,

To do this you will need a cell that tracks today's date:

=today()

and you will need to enter the date you enter the "event" in, perhaps in the
cell next to it. (A shortcut to doing this is CTR-; control-semicolon.) If
you perfer, you can format the cells of the dates you enter to be the same
color as the background so they are not visible. Using that date and the
actual date you can put a formula in your conditional formatting (my example
is using excel 2007).

In my example I have the events starting in B2 and the date of entry in C2.
In conditional formatting I will select the cells in B I want to format then
select Conditional Formatting. Under New Rule I will select Use Formula...
Put the formula:

=$A$1-C2>=30

then select the Format button to make your format choices.

Let me know if you have problems.

Squeaky



"hadeeter" wrote:

> I'll do my best to explain my question, please let me know if more
> information is needed.
>
> I have a "calendar" of sorts that is in Excel format. It lists several
> events, dates that the events will take place, and other pertinent
> information. I have been asked to format the calendar in such a way that when
> I enter in a new event, it will be in red text for 30 days, and then convert
> to "normal" or black text after being in the spreadsheet for 31 days.
>
> Is there a way to set this up with Conditional Formatting? Everything I have
> found relates to the date actually typed into the spreadsheet. I need it to
> be from the date the data is entered (regardless of the date typed in the
> field).
>
> Thanks in advance for any help.
 | 
Pages: 1
Prev: Formula to breakdown original amount with two components to the pe
Next: Data validation