2

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?

Improve this question

1 Answer 1

Reset to default
0

Can't you just use:

=ARRAYFORMULA(TO_DATE(IF(LEN(A3:A),VLOOKUP(A3:A,Data!A3:B,2,0)*1,"")))
Improve this answer
1
  • 1
    Thanks! This seems to work perfectly. It seems I may have been using the TO_DATE() formula in the wrong location. I have noticed the multiplication action forced the text to a number, I will have to remember this trick.
    – Alsors
    Dec 1, 2020 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.