Conditional formatting dates dates past 4 years 2

[venim]

Hi,

I have a sheet containing information on our computer park, and now I wanted to automatically show those PC's that are older than 4 years.
The delivery date of the PC's is in column C. I've tried a conditional format with "Formula is" and "=(TODAY() - $C1)>1461" but that didn't work (1461 days is more or less 4 years)
If anyone can help me out here...

Thanks for your time!

 

[GlennUK]

Are the entries proper Excel dates, or text?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[venim]

They're formatted as dates in excel (format dd/mm/yyyy)

 

[combo]

Try with 'Formula is':
=((TODAY() - $C1)>1461)

combo

 

[macropod]

Hi venim,

Try using:
=YEAR(TODAY()-C1)>1903

Cheers

[MS MVP - Word]

 

[venim]

Thanks guys but both formulas for some reason also mark dates only a few month ago...

 

[macropod]

In that case, you don't have a date in C1 - you've got a text string masquerading as a date - probably with one or more space characters.

Try:
=YEAR(TODAY()-TRIM(C1))>1903

Cheers

[MS MVP - Word]

 

[combo]

1. Test the formula in a cell. Should return TRUE or FALSE. See, what the conditions are for given output.
2. What is the output for =C1+1 formula (in any free cell)? Can you format it as date or number, and the way it is displayed changes?
3. Do you have any other conditional formats with higher priority defined in the cell? If so, excel will use the first that results TRUE, whatever other are.

combo

 

[venim]

Thanks Macropod and Combo! playing around with Macropod's answer and testing it the way Combo suggested did the job!

 

[GlennUK]

So your entries were text?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red][/red]
[/sub]