Convert this text into a useable date format

[Mark F]

Hi

I have tried various things, but I am sort of admitting defeat!

In column A I have text fields in the format April 1, 2023 08:30

I am looking to convert this into a useable Excel date format and time format, in Column B. 01/04/2023, or 45017

I know it should be simple, but I can't work it out.

Any help appreciated

Mark

 

[Cubist]

Select data -> Text-to-Column -> Delimited -> Uncheck all -> Date (MDY) -> Finish.

 

[Mark F]

Thanks,

It's a good idea, and one I hadn't considered, but for the data I have that option isn't working unfortunately. Next is greyed out.

 

[Cubist]

Your data is MDY and hit finish.
Another option is to try:

=DATEVALUE(A1)

 

[Dave Patton]

Date prel.xlsx
	A	B	C
1
2	April 1, 2023 08:30	1-Apr-23 8:30	1-Apr-23 8:30
3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=--CONCAT(INDEX(TEXTSPLIT(A2,{" ",","},,1),{2,1,3}))+RIGHT(A2,4)
C2	C2	=LET(x,TEXTSPLIT(A2,{" ",","},,1),DATE(INDEX(x,3),MONTH(1&INDEX(x,1)),INDEX(x,2))+INDEX(x,4))