I am currently pulling out my hair because I cannot seem to understand why these date cells aren't being converted to the date format when the TO_DATE() formula is used. Here is my example sheet (IDs are randomly generated in this case).
The Data sheet is just that, it is static data that is manually updated. Please note the dates in the Data sheet have a ' prefix. The B3 cell in the VLOOKUP sheet is the formula I am struggling with. I have written the formula to remove the ' prefix and convert the text to date formatting, unfortunately either I have done something wrong (quite likely) or Google is toying with me.
Does anyone know how to fix this issue?