# Formula to show a date based on a proximity score

#### halesowenmum

##### Active Member
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.

Last edited by a moderator:

#### halesowenmum

##### Active Member
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

##### MrExcel MVP, Moderator
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

##### Active Member
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

##### MrExcel MVP, Moderator
.. 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

##### Active Member
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??).

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
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>

Last edited:

#### halesowenmum

##### Active Member
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

##### MrExcel MVP, Moderator
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

##### Active Member
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.

1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...