I currently have the text variable [Rating Start Date] which assumes one of two values: a) Either a value of "No Existing Value", or b) a date value of ddmmyyyy.
What I am trying to do is retain the values of "No Existing Value" but convert all of ddmmyyyy formats into mm/dd/yyyy formats. Below is the formula that I've been using. I also offer samples of current and desired output in the attached Excel file.
IIf([Rating Start Date]="No Existing Value","No Existing Value",CDate(Format([Rating Start Date],"mm/dd/yyyy"))) AS Test