Formula to show a date based on a proximity score

[halesowenmum]

Hi all.

I have a document in which a score (1 to 5) is used to indicate a time period in which it is felt a risk would be likely to occur. Closer that risk is to actually happening, the higher the score. The scoring is as follows and is entered manually by the user:

5 is within a month, 4 within two months, 3 withinthree months, 2 within six months, 1 is over 6 months

As I was working on this document I was thinking that firstly, it would be good if the date on which that three months or six months falls, was generated using a formula, instead of it being manually typed in. I could then use CF to make the most imminent ones at any time, be well highlighted for users to see.

If the proximity score was altered (say the team had worked to extend a deadline and so now the proximity isn't 4, it's 2) then the date would recalculate when that score was adjusted. I think that could be done with a formula and CF combo?

So as part 2 of this question, what would be the best thing, based on the formula suggested for in the spreadsheet, to put into the CF to get different colours and fonts of my choice based on whichever score is picked - I need to know whether to use a formula-based CF or another way, so that I can select different colours and fonts for each score that will apply based on the proximity date.

Thank you very much indeed.

 

[Peter_SSs]

That is working absolutely brilliantly - this will really help pick out the risks that need priority review so they can be focused on first.

Signing off with thanks, one very happy PMO Manager.

You're welcome. Thanks for the enthusiastic follow-up.