Get date/date with text, from the set range

Hello,

Green date - training done. Red with tt - training planned. Get cell with tt (if more than one tt cell for that job, get tt with an earliest future date from them) if no tt in one job range, grab date on which training was completed latest. It makes more sense on the attached file.

I was trying something like this but got lost

HTML Code: 

Many Thanks for any suggestions

Thanks a lot saravnepali, I appreciate your help!