Share via

converting dates as text into a usable date format in excel

crib bar 856 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

Answer accepted by question author

Jiajing Hua 18,100 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.

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2025-08-01T23:45:20.8533333+00:00

    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.

    User's image

    Was this answer helpful?

    0 comments
  2. Olaf Helper 47,616 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

    Was this answer helpful?

    0 comments

Your answer

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