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?




1 Reply


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.