Convert drop down in text format to date format


I have seen various other questions in the community, but I can't quite get my equation there, I am trying to convert a drop down from a form to a date in the adjacent column. All dropdown items are formatted as follows:


Here is what I am trying:

=DATE(VALUE("20" + RIGHT([Testing Date]@row, 6, 7)), VALUE(MID([Testing Date]@row, 3, 4)), VALUE(LEFT([Testing Date]@row, 1)))

any thoughts on why I can't get this to work?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!