Convert general string to date format

[thelad]

Hi,

Is it possible to convert general format cell to a date format? for example in cell A1 is Oct 1 2023 12:00AM i want to convert this to be 01/10/2023 date format. To note all the cells will have same 12:00AM at end.

Example:
Oct 1 2023 12:00AM become 01/10/2023
Nov 30 2023 12:00AM becomes 30/11/2023

 

[Joe4]

One way (at least on American systems).
If your value is in cell A1, use this formula:

=DATEVALUE(LEFT(SUBSTITUTE(A1," 12:00AM",""),LEN(SUBSTITUTE(A1," 12:00AM",""))-5) & ", " & RIGHT(SUBSTITUTE(A1," 12:00AM",""),4))

and then format the cell to "dd/mm/yyyy"

 

[RustamMika]

thelad,​

try this:

Sub NNN()
    [A1] = Format([A1].Value, "dd/mm/yyyy h:mmAM/PM")
End Sub

 

[Alex Blakenburg]

Please update your Account Profile to indicate what version of Excel you using.
If you have MS 365 something like this might work for you.

=LET(dt,TEXTSPLIT(A1," "),
DATEVALUE(INDEX(dt,,2)&"-"&INDEX(dt,,1)&"-"&INDEX(dt,,3)))

 

[Scott Huish]

=DATEVALUE(LEFT(SUBSTITUTE(A1," ",", ",2),12))

Format as date

 

[thelad]

=LET(dt,TEXTSPLIT(A1," "), DATEVALUE(INDEX(dt,,2)&"-"&INDEX(dt,,1)&"-"&INDEX(dt,,3)))

Hi, Thanks this worked perfect. Under replies did not return anything.

 

[Scott Huish]

It should've

Book1
	A	B
1	Oct 1 2023 12:00AM	01/10/2023
2	Nov 30 2023 12:00AM	30/11/2023
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=DATEVALUE(LEFT(SUBSTITUTE(A1," ",", ",2),12))

 

[Dave Patton]

N.B. This system has International Date settings dd-mmm-yyyy.

Dates Time.xlsm
	A	B
1	Oct 1 2023 12:00AM	01/10/2023
2	Nov 30 2023 12:00AM	30/11/2023
3
4m

Cell Formulas
Range	Formula
B1:B2	B1	=--CONCAT(INDEX(TEXTSPLIT(A1," ",,1),{2,1,3}))