converting dates as text into a usable date format in excel

crib bar 846 Reputation points
2024-02-15T14:29:37.62+00:00

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.

Microsoft 365 and Office Install, redeem, activate For business Windows
Microsoft 365 and Office Excel For business Windows
{count} votes

Accepted answer
  1. Jiajing Hua 18,060 Reputation points Moderator
    2024-02-16T08:31:50.52+00:00

    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.

    User's image


    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.


    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 47,436 Reputation points
    2024-02-16T08:37:26.42+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.