Formula needed to convert a text based date to an M/D/YYYY format

[jkinnick]

Hi,

My sorce data date is May 2 2019 12:00AM and in a text format. I want to convert it to a date format of M/D/YYYY using a formula so that it reads 5/2/19. This formula must also work for double-digit days like May 13 2019 12:00AM will read 5/13/2019. I cannot change my source data so I need a formula that can do this in one fell swoop. Will someone please help me to write a formula that can do this?

Thanks,

 

[Fazza]

hello

to understand which bit you want help with, it would help to see what you've done

regards

 

[Fazza]

does this work for you?

=TEXT("2-"&LEFT(A1,FIND(" ",A1)-1)&"-2019","M")&""&TRIM(MID(A1,FIND(" ",A1)+1,2))&""&MID(A1,FIND("20",A1)+2,2)

 

[jkinnick]

does this work for you?

=TEXT("2-"&LEFT(A1,FIND(" ",A1)-1)&"-2019","M")&""&TRIM(MID(A1,FIND(" ",A1)+1,2))&""&MID(A1,FIND("20",A1)+2,2)

Thank you that worked! I just added the two /'s into the formula and it works great:

=TEXT("2-"&LEFT(A1,FIND(" ",A1)-1)&"-2019","M")&"/"&TRIM(MID(A1,FIND(" ",A1)+1,2))&"/"&MID(A1,FIND("20",A1)+2,2)

 

[mikerickson]

If your data is in A3, then
=DATEVALUE(LEFT(SUBSTITUTE(A3," ",", ",2),FIND(":",A3)-2))

will return the excel serial date. The cell holding the formula can be formatted to your taste.