Two-Part Problem - Complex Formula and Conditional Formatting

Hi all,

I'm hoping you can help me with an issue I'm trying to overcome. As the title indicates, this is a two-part problem.

The attached document shows a Goal Tracker, (see Current tab) which until now served only to indicate whether goals had been achieved, were still pending, or missed their Planned Dates. I've recently been asked to incorporate a new function, wherein if a goal was missed, a New Date and New Status were to be added against the same goal, preferable against the same line - see the Ideal tab of the attached document.

My issues are the following:

1. I am currently using the following formula between F2:Q11 [=IF(EOMONTH([@[Planned Date]],0)=EOMONTH(F$1,0),"u","")] to place a milestone against the original planned date. My issue is I can't figure out how to expand / change the formula to include the second milestone, should the first one be categorised as Missed.

2. The second issue has to do with Conditional Formatting. At the moment the F2:Q11 have conditional formatting that changes the colour of the milestone based on the Status under Column C. I need the new milestone to ignore that particular rule and focus on the Status (and respective conditional formatting) assigned under "New Status".

A colleague suggested I try using the IFS function to overcome the first problem, however I'm unfortunately working with a version of Excel2016 that doesn't allow it.

I'm at a loss to understand how to overcome any of these issues. Any ideas and suggestions would be most welcome!

Kind regards,
G

How would you decide what the new date would be? That would help with the formula generation since you are establishing a new goal date. What, five more business days?

Hi, sorry, should have made that more clear. The new date would be selected from the drop-down list under "New Date" column. Goals are only tracked on a monthly basis I'm afraid, so if not October (for instance), then November or any other subsequent month.

I think the problem is you are applying the formats to all cells I would change the formula to the below

=IF(EOMONTH(Table42[@[Planned Date]],0)=EOMONTH(M$1,0),"u",IF(EOMONTH(Table42[@[New Date]],0)=EOMONTH(M$1,0),"l",""))

and have a circle for revised dates

then change the conditional formats as
=AND($C2="Missed",F2="u")

then create new formats
=AND($e2="Missed",F2="l")

etc

Thank you so much, that worked perfectly!