How to convert text into date?

Occasional Contributor

I have the following data in my data column:

 

October 09 2020 / 5 days

 

And I want to write a formula that will convert this into the format DD/MM/YYYY so that I can use it as a date.

 

How would I go about this?

 

Thanks,

Morgan

1 Reply

@morgancampbell 

Let's say you have such a text string in A2.

Enter the following formula in another cell in row 2:

 

=DATEVALUE(MID(A2,FIND(" ",A2)+1,2)&"-"&LEFT(A2,FIND(" ",A2)-1)&"-"&MID(A2,FIND(" ",A2)+4,4))

 

Format the cell with the formula as a date.

Fill down.