I have a spreadsheet with names and dates. They want a date to turn
color or do something when the date hits 3 years old. So if I did something
on 1.1.04 they want it to indicate expired when 1.1.07 hits.
Any suggestions would be greatly appreciated.
Thanks!!!!

[klaus]

One way would be to put today's date, =today(), in a cell of your choice.
insert a column (assuming you have your data in columns) where you substract your date from today's date (set cell format to general to see the value in days)
then do a conditional formatting of your date column column to change color when the difference in dates exceeds target value in days.

Sure... Using Conditional Formatting, assuming date is in A1:

Format... Conditional Formatting...
Hit Drop down to say Formula is
=if(now()-a1>1095,1,0)
Click on Format to change to desired result.
That should do it.



KenRamoska wrote:
> I have a spreadsheet with names and dates. They want a date to turn
> color or do something when the date hits 3 years old. So if I did something
> on 1.1.04 they want it to indicate expired when 1.1.07 hits.
> Any suggestions would be greatly appreciated.
> Thanks!!!!

There could be a leap year within that 3-year span (generally), so
maybe better to make it:

=if(now()-a1>1096,1,0)

Hope this helps.

Pete

willwonka wrote:
> Sure... Using Conditional Formatting, assuming date is in A1:
>
> Format... Conditional Formatting...
> Hit Drop down to say Formula is
> =if(now()-a1>1095,1,0)
> Click on Format to change to desired result.
> That should do it.
>
>
>
> KenRamoska wrote:
> > I have a spreadsheet with names and dates. They want a date to turn
> > color or do something when the date hits 3 years old. So if I did something
> > on 1.1.04 they want it to indicate expired when 1.1.07 hits.
> > Any suggestions would be greatly appreciated.
> > Thanks!!!!

AWESOME!!!!!!!!!!!!!!

"willwonka" wrote:

> Sure... Using Conditional Formatting, assuming date is in A1:
>
> Format... Conditional Formatting...
> Hit Drop down to say Formula is
> =if(now()-a1>1095,1,0)
> Click on Format to change to desired result.
> That should do it.
>
>
>
> KenRamoska wrote:
> > I have a spreadsheet with names and dates. They want a date to turn
> > color or do something when the date hits 3 years old. So if I did something
> > on 1.1.04 they want it to indicate expired when 1.1.07 hits.
> > Any suggestions would be greatly appreciated.
> > Thanks!!!!
>
>

........ alternative method is to create a RangeName given a value of
=EDATE(TODAY(),-36)

........then do a CF of "CellValueIs", "less than or equal to",
=YourRangeName

Vaya con Dios,
Chuck, CABGx3


"KenRamoska" wrote:

> I have a spreadsheet with names and dates. They want a date to turn
> color or do something when the date hits 3 years old. So if I did something
> on 1.1.04 they want it to indicate expired when 1.1.07 hits.
> Any suggestions would be greatly appreciated.
> Thanks!!!!