Extracting a date from a text field

Stéphane Charbonneau

Ok, now... I have this data in one text column "2021-09-14 12:02:51" and I'm tryng to extract the date only (i.e. 2021-09-14) from this data and have it stored in a DATE column in the same sheet. I've tried many different ways but just can't make it work... I'm getting frustrated ;)

Kelly Moore

Hi @Stéphane Charbonneau

Since the number of characters will remain fixed, we can use the character count directly. To convert text to date, you must use the DATE function, which has the format of DATE(YYYY, MM, DD). We will gather this information using the LEFT and MID functions. To make sure the result is recognized as a number and not text, we'll force it to a number using the VALUE function

=DATE(VALUE(LEFT([your text column]@row, 4)), VALUE(MID([your text column]@row, 6, 2)), VALUE(MID([your text column]@row, 9, 2)))

Be sure to insert your actual column name into the formula.

Did this work for you?

Kelly