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.

 

[halesowenmum]

Just wondering if anyone has any ideas.

Been scouring the internet but all formulas that might work are based on using a date to produce some other date result whereas this is using a value to display a date... Sadly I have no idea how to construct the formula so super hopeful someone can help.

 

[Peter_SSs]

Does this get us headed in the right direction?

<b>Risk Period</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:112px;" /><col style="width:95px;" /><col style="width:129px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Some Date</td><td style="font-size:10pt; text-align:center; ">Risk period</td><td style="font-size:10pt; ">Risk period End</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">14-Apr-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">14-Jun-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">09-Jun-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">09-Sep-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">10-Jan-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">No End</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">18-Mar-20</td><td style="font-size:10pt; text-align:center; ">5</td><td style="font-size:10pt; text-align:right; ">18-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">30-Dec-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">28-Feb-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">07-Oct-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">No End</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">10-Feb-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">10-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">26-Apr-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">26-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">24-Dec-20</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:right; ">24-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">12-Oct-20</td><td style="font-size:10pt; text-align:center; ">5</td><td style="font-size:10pt; text-align:right; ">12-Nov-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">09-Mar-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">No End</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">04-Jan-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">04-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">13-Apr-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">No End</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">22-Jul-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">No End</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">08-Jan-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">08-Mar-20</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IFERROR(EDATE<span style=' color:008000; '>(A2,CHOOSE<span style=' color:#0000ff; '>(B2,"",6,3,2,1)</span>)</span>,"No End")</td></tr></table></td></tr></table>

 

[halesowenmum]

Hi Peter and once again many thanks for your kind response and yes, we're on our way.

For the items with a 1, I think we would need a date that was actually what the first date within the 'anything over six months' stipulated period, ie, either the first day of the commencement of that six month period or the day after that if you want to be technically accurate/a bit picky )) - so you'd still get a date. Could the formula be adjusted to do it that way??

Would I need a separate formula to use in the CF so I could highlight the populated dates to show the highest and lowest proximities with colour?? If so what would that formula look like?

Regards.

 

[Peter_SSs]

.. yes, we're on our way.

Great!

For the items with a 1, I think we would need a date that was actually what the first date within the 'anything over six months' stipulated period, ie, either the first day of the commencement of that six month period or the day after that if you want to be technically accurate/a bit picky )) - so you'd still get a date. Could the formula be adjusted to do it that way??

Yes, I think this is what you mean. It does seem a bit strange to me though as for risk periods 2-5 we have an end date but for risk period 1 we have a start date.

<b>Risk Period</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84px;" /><col style="width:84px;" /><col style="width:161px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Some Date</td><td style="font-size:10pt; text-align:right; ">Risk period</td><td style="font-size:10pt; text-align:right; ">Risk period End/Start</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">14-Apr-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">14-Jun-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">09-Jun-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">09-Sep-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">10-Jan-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">11-Jul-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">18-Mar-20</td><td style="font-size:10pt; text-align:center; ">5</td><td style="font-size:10pt; text-align:right; ">18-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">30-Dec-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">28-Feb-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">07-Oct-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">08-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">10-Feb-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">10-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">26-Apr-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">26-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">24-Dec-20</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:right; ">24-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">12-Oct-20</td><td style="font-size:10pt; text-align:center; ">5</td><td style="font-size:10pt; text-align:right; ">12-Nov-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">09-Mar-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">10-Sep-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">04-Jan-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">04-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">13-Apr-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">14-Oct-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">22-Jul-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">23-Jan-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">08-Jan-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">08-Mar-20</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=EDATE(A2,CHOOSE<span style=' color:008000; '>(B2,6,6,3,2,1)</span>)+(B2=1)</td></tr></table></td></tr></table>

.. the CF so I could highlight the populated dates to show the highest and lowest proximities with colour??

You would have to elaborate (examples?) on jusr what you mean by the blue part. Do you just mean highlight any 5 or 1 risk priorities?

 

[halesowenmum]

I get what you're saying but I actually think it's absolutely right and what we want in this case (oddly enough).

In going off to apply it though, the 'some date' bit is giving me a problem.

On this document I've got Date Raised (these are risks that have to be managed) - so that's great because for most risks it's at the point of raising the risk that the user will enter the period of time that they think that risk could likely manifest (using the 1-6 scores). I'm trying to figure out,

I'd like to highlight the 5s and 4s, ie the two date periods that are the closest, that would be ideal, they're the ones that should be getting the most attention.

Is there any additional we can add to the formula to say "Enter Date Raised' where there is no value in that column (rather than it returning an erroneous type of date??).

 

[Peter_SSs]

Try this

<b>Risk Period</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84px;" /><col style="width:84px;" /><col style="width:161px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Some Date</td><td style="font-size:10pt; text-align:right; ">Risk period</td><td style="font-size:10pt; text-align:right; ">Risk period End/Start</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">14-Apr-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">14-Jun-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">09-Jun-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">09-Sep-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">10-Jan-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">11-Jul-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">18-Mar-20</td><td style="font-size:10pt; text-align:center; ">5</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">18-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; ">Enter Date Raised</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">07-Oct-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">08-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">10-Feb-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">10-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">26-Apr-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">26-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">24-Dec-20</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:right; ">24-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">5</td><td style="font-size:10pt; ">Enter Date Raised</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">09-Mar-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">10-Sep-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">04-Jan-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">04-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">Enter Date Raised</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">22-Jul-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">23-Jan-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">08-Jan-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">08-Mar-20</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(A2="","Enter Date Raised",EDATE<span style=' color:008000; '>(A2,CHOOSE<span style=' color:#0000ff; '>(B2,6,6,3,2,1)</span>)</span>+<span style=' color:008000; '>(B2=1)</span>)</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >C2</td><td >1. / Formula is =AND(A2<>"",B2>=4)</td><td style="background-color:#ffc000; ">Abc</td></tr></table></td></tr></table>

 

[halesowenmum]

Thank you. spreadsheet formula working well.

Conditional formatting - am I putting this in 'Use a formula' area, and do I type in the 1./Formula is bit too - I've tried with and without and neither results in coloured shading.

Is it possible to explain further how I should enter the formula in the CF, and is it possible to have the abiilty in the formula to allow me to have one colour for the 5s, and another for the 4s?

 

[Peter_SSs]

Is it possible to explain further how I should enter the formula in the CF, and is it possible to have the abiilty in the formula to allow me to have one colour for the 5s, and another for the 4s?

Sure. For the layout below ..

1. Select from C2 down to the end
2. Home ribbon tab - Conditional Formatting - Clear Rules - Clear Rules from selected cells (This is just to ensure anything that you tried before is cleared out)
3. Home ribbon tab - Conditional Formatting - New rule ... - Use a formula to determine which cells to format (You were doing this already I believe) - In the 'Format values where this formula is true:' box enter only the formula (I have highlighted it red this time) - Format... - Fill tab - Choose colour - OK -OK
4. Repeat for the second condition/colour

<b>Risk Period (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84px;" /><col style="width:84px;" /><col style="width:161px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Some Date</td><td style="font-size:10pt; text-align:right; ">Risk period</td><td style="font-size:10pt; text-align:right; ">Risk period End/Start</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">14-Apr-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">14-Jun-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">09-Jun-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">09-Sep-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">10-Jan-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">11-Jul-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">18-Mar-20</td><td style="font-size:10pt; text-align:center; ">5</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">18-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">4</td><td style="font-size:10pt; ">Enter Date Raised</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">07-Oct-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">08-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">10-Feb-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">10-Apr-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">26-Apr-21</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">26-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">24-Dec-20</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:right; ">24-Jun-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">5</td><td style="font-size:10pt; ">Enter Date Raised</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">09-Mar-21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">10-Sep-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">04-Jan-20</td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">04-Apr-20</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; ">Enter Date Raised</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">22-Jul-20</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">23-Jan-21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">08-Jan-20</td><td style="font-size:10pt; text-align:center; ">4</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">08-Mar-20</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(A2="","Enter Date Raised",EDATE<span style=' color:008000; '>(A2,CHOOSE<span style=' color:#0000ff; '>(B2,6,6,3,2,1)</span>)</span>+<span style=' color:008000; '>(B2=1)</span>)</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >C2</td><td >1. / Formula is =AND(A2<>"",B2=5)</td><td style="background-color:#00b0f0; ">Abc</td></tr><tr><td >C2</td><td >2. / Formula is =AND(A2<>"",B2=4)</td><td style="background-color:#ffc000; ">Abc</td></tr></table></td></tr></table>

 

[halesowenmum]

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.