how to convert a text date to a date

MarkCep

I have a column that brings in a date and time

ex 08/22/2024 05:30:22 PM

I want to extract the date portion into a date column, but I keep getting a #invalid Operations.

DATEONLY will not work because this is not a system generated date, it is coming in from another system.

ex

=LEFT([Inquiry Date]@row, 10)

does not work

Help

Matt Johnson

Hi @MarkCep

Your situation is probably different than the test I just set up but…if the column that's being populated from another system is Text/Number and the column that needs the date is a date column, the LEFT formula you posted worked for me in the date column.

Given your star ranking, I figure something else is going on but just wanted to pass on my test results.

Matt

MarkCep

Thanks Matt, I was having a brain block but figured it out.

Working formula:

=DATE(VALUE(MID([Inquiry Date]@row, 7, 4)), VALUE(MID([Inquiry Date]@row, 1, 2)), VALUE(MID([Inquiry Date]@row, 4, 2)))