Help With Date Stored as text to Date

[maximillianrg]

Hello Excel Masters

I'm looking for a formula to convert the below text in cell A1 into the date Mar 20

A1 = March 20, 2023 at 10:21AM

The formula needs to work for any month and day, for example in 6 months the formula would convert September 21, 2023 at 11:21AM

Thank you in advance

 

[Joe4]

Try this formula:

=TEXT(DATEVALUE(LEFT(A1,FIND(" at",A1)-1)),"mmm dd")

that will return a TEXT value.

If you want a valid date value you can do computations on, use this instead:

=DATEVALUE(LEFT(A1,FIND(" at",A1)-1))

and then just format the cell with a Custom Date Format of "mmm dd".