converting dates as text into a usable date format in excel

Question

I've received an xlsx file and one column represents date/time, but the data is currently recorded as general text in format: "Tuesday, 30 January 2024, 9:52 PM" Is there any obvious method to get this type of textual date info into an actual date/time that Excel recognises as date so I can use the data filters to filter on the data. Obviously I could use text to columns feature to get the actual UK dd/mm/yyyy part (the middle 'section') which is actually sufficient in this case, but there are still blank spaces between the days, month and year which I suppose could be 'searched and replaced' to form something resembling a valid date. Any more efficient conversion techniques would be useful for future reference, but gut feeling is it may take a few individual steps and cannot be easily resolved in a single formula.

Answer 1

Hi @crib bar

Please try the formula =DATEVALUE(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),100,100))) to convert the date. Please note, do not forget to format the result cells as date format.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Why not simply define a cell form for the date in a way you prefer? See MS Support Format a date the way you want

Answer 3

Hi,

In cell B2, type py and tab. Then enter this formula

pd.to_datetime(xl("A2"))

Confirm with Ctrl+Enter. In the drop down to the left of the formula bar, select Excel value.

Hope this helps.