I have a drop down list in place in column B, because of which only 5 options are available.

options present are:

submitted
completed
not received


now I need to check the date and change the drop down value.

so if a document was supposed to be received on 1/1/14 and today is 1/14/14, then the entry should automatically get changed to "Not received".


I would prefer if this can be done using conditional formatting or some formula that will not disrupt the dropdown list.

Please assist