Check if project milestones have met their expected deadlines

Hello, first time poster in the forum, hope this is the right place.

I have sanitised, simplified and attached a sheet that I could really use some help with.

At any given date that I open this sheet, I would like to know whether 2 aspects of each project ( identified in this sheet by Col C - CRF Ident ) have been completed in line with an agreed timeline. Aspect 1 SIA Response (col D) should be completed up to and including 2 weeks after launch date (col A), and Aspect 2 Estimate response (col E) should be completed up to and including 6 weeks after launch date (col A)

Completion is indicated by an entry of 'DONE' in Col D & E. Any other value including blanks denote that the milestone has not yet been completed.


So ideally I need a formula or method that I can easily replicate use in my original un-sanitised data sheet of inserting yes in to column F if (col D = Done) AND (Col A is less than or equal to 14 days from todays date), and inserting yes in to column G if (col E = Done) AND (Col A is less than or equal to 42 days from todays date). If the conditions are not met then a No instead of a yes. Manual examples shown for first 3 lines.

Hope this makes sense and that someone can help

I've done similar I think in attached.