Formula to show a date based on a proximity score

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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
Joined
May 28, 2005
Messages
42,177
Office Version
365
Platform
Windows
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
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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
Joined
May 28, 2005
Messages
42,177
Office Version
365
Platform
Windows
.. 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. :confused:

<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
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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
Joined
May 28, 2005
Messages
42,177
Office Version
365
Platform
Windows
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
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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
Joined
May 28, 2005
Messages
42,177
Office Version
365
Platform
Windows
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
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

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...
Top