How to convert text into date?

%3CLINGO-SUB%20id%3D%22lingo-sub-1775040%22%20slang%3D%22en-US%22%3EHow%20to%20convert%20text%20into%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775040%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20data%20in%20my%20data%20column%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOctober%2009%202020%20%2F%205%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20want%20to%20write%20a%20formula%20that%20will%20convert%20this%20into%20the%20format%20DD%2FMM%2FYYYY%20so%20that%20I%20can%20use%20it%20as%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20I%20go%20about%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EMorgan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1775040%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1775127%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%20text%20into%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F789072%22%20target%3D%22_blank%22%3E%40morgancampbell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20such%20a%20text%20string%20in%20A2.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20another%20cell%20in%20row%202%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATEVALUE(MID(A2%2CFIND(%22%20%22%2CA2)%2B1%2C2)%26amp%3B%22-%22%26amp%3BLEFT(A2%2CFIND(%22%20%22%2CA2)-1)%26amp%3B%22-%22%26amp%3BMID(A2%2CFIND(%22%20%22%2CA2)%2B4%2C4))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20formula%20as%20a%20date.%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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.