Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am working on a spreadsheet that logs the dates parts were sent out and would like to apply conditional formatting to the dates.

I would like if over 3 years ago, if over 2 years ago, if over 1, if this year... then format appropriately. I have looked around Google and found some formulas but not the ones I need/work. So far I have this, but it doesn't work as expected:

3 years  : =TODAY()-$E$26<=1065    
2 years  : =TODAY()-$E$26<=730    
1 year   : =TODAY()-$E$26<=365    
This year: =TODAY()-$E$26<=1

I fixed the years by changing the < sign to >, my mistake - but am still having trouble with the if this year. I would expect something like if < 365 to work, but it doesn't. For example, May 2011 is displaying the formatting for less than a year, even though it's not.

share|improve this question
Ok, all fixed- wouldn't let me answer question myself yet less than 1 year =TODAY()-$E$26<=365 1 year = =TODAY()-$E$26>=365 2 years = =TODAY()-$E$26>=730 3 years =TODAY()-$E$26>=1065 – ben Jul 23 '12 at 9:35
glad you figured it out – datatoo Jul 23 '12 at 15:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.