Microsoft Excel again

[367squadron]

I want some way of displaying or marking a date that is over 7 years old in a database, surely it's possible but how?

 

 

Thanks.

[UKSentinel]

sounds like a conditional formatting rule. I'll have a play around and see if I can come up with something

[367squadron]

sounds like a conditional formatting rule. I'll have a play around and see if I can come up with something

 

Thank you, i'd be really grateful!

[Tricky]

Cell value is less than NOW()-2557 using conditional formatting, where 2557 is 365.25 x 7years rounded.

 

If you want to pull the result into another column...=IF(NOW()-ROUND(7*365.25,0)>A1,"X","") where your date is in A1

[UKSentinel]

I was thinking more on the lines of using conditional formatting under the format tool bar to show the date in red where the date was greater than 7 years old. So much simpler. However, I've just produced the result but not sure how to explain it here, lol

[367squadron]

Cell value is less than NOW()-2557 using conditional formatting, where 2557 is 365.25 x 7years rounded.

 

If you want to pull the result into another column...=IF(NOW()-ROUND(7*365.25,0)>A1,"X","") where your date is in A1

 

Lol thanks, good idea but for some reason it is just putting all dates in red therefore not picking up the ones over 7years.

[367squadron]

I was thinking more on the lines of using conditional formatting under the format tool bar to show the date in red where the date was greater than 7 years old. So much simpler. However, I've just produced the result but not sure how to explain it here, lol

 

I know what you mean i have just put in a date that was 7 years ago and it did that fine but what i need is Excel to look at todays date and tomorrows when i open it again to automatically calculate that the date over 7 years old.

 

Its some file that i have to shred but an't do it until they are 7 years old, some are some arent that's why i need them highlighted.

[367squadron]

Anyone else? Still havent sorted this out!

[steve_sufc]

Do you need to do it in Excel? This would be way easier creating a table in MS Access and performing a select query on it.

[Tricky]

Lol thanks, good idea but for some reason it is just putting all dates in red therefore not picking up the ones over 7years.

 

Seems to be a formatting issue. If you select the cells, go into format cells and select the third line down for the date format, it works fine.