Data / Cell Validation

Hi everyone, am looking for a little help, I have a spreadsheet that I am using to Asset and PAT test track, I have a number of columns, the ones I am trying to automate are the Dates, what type of test is due, is a test required. eg. (I have uploaded a sample if it is helpful)

Test frequency - 12 / 48 Months - 12 is Visual test/ 48 is Full test

Last test Date(vis) - 27/04/2010 - Test next Due 06/04/2011

Last test date (Ful) - 27/10/2010 - Test next due - 06/04/2010

I have put a conditional format that turns cell red/green dependant if test is due or not, the next two colums that i am trying to work out are

Is test required - Yes / No / Not Req
Test Type - Visual / Full / Not Req

Thanks

Apologies - Question is how can i get the Test Requried Colum to puplate with Yes, No or Not Required and the Test Type Colum to populate with Visual, Test or Not Required , dependant on the entries in the date columns