Any EXCEL experts around?

[Glasstine]

I manage a spreadsheet containing the info of around 500 people

They need to come off after 5 years from the date they were initially entered

What I would ideally have is some kind of colour coded system so if they have gone past 5 years they go red, if they are due to come off within 6 months they go amber and all the rest could be green

But I already have conditional formatting set up which fills them grey once they have been removed from active

Could I tack up a separate column to be colour coded depending on their dates? This is where I get lost and can’t think straight!

Would appreciate any advice please

Lots of people with Excel skills over on this thread: www.mumsnet.com/Talk/am_i_being_unreasonable/3356101-What-has-been-the-most-useful-skill-in-your-working-life
Maybe you could post there? Sorry, can’t help myself.

I think an IF function may be of use.

Does the spreadsheet have a column with the date they were added?

If so I think you could do a column with conditional formatting, with =TODAY()-xx where xx is date addedwith colour cell if > 3655, and then repeat with orange for 3654.5 ?

Do you need to colour code things though or just remove them? You could sort by the date entered column and just delete the rows > 5 years old. Sorry if I'm missing the point.

I can't think how to do it without adding two columns, one with the date they entered that you could then collapse/ hide. You could the either have a separate "status" box to conditionally format to red/ green or set up a string of IF to spit out "pending" or something similar depending on date. Or apply the colour format to their names as I imagine that's fairly prominent in your sheet. As far as I know EXCEL don't have an inbuilt conception date for data entry to refer to, if that's what you were wondering? You'd need to manually record then hide it.

Try asking Help about Icon Sets - I can't remember the exact way to do it, but I think you need to select either New Rules or More Rules. One thing to remember in the "Edit the Rule description" I think from memory is you need to select "Show Icon only" once you have select the icon you want to appear (ie the traffic light system) and the dates (numbers etc) in the values box. Sorry this is the best I can do at the moment - I don't have Microsoft on the laptop. Good luck,

I think you can have more complex conditional formatting rules.

I’d also play around a bit more with the conditional formatting

Seconding Nacreous in the suggestion of using the today function, but nesting it in an if statement to return a text saying something like "Delete" "6 months"

Just tested this and it seems to work:
=IF(TODAY()-$A10>(3655),"delete",IF(TODAY()-$A10>(3654.5),"6 months",""))

Then apply conditional formatting to the text values.

There is already a column with the start date

But you are all amazing! Thank you so much, this is why I love MN!!