Forum Discussion
Convert Date/Time String to Date Format
I am seeking assistance with converting a string that is a date/time value into a date format in Excel.
I would like to use this field along with associated data in a pivot table to group by day, week, month, etc...
I would like to have only the date portion of the string formatted as mm/dd/yyyy. Any assistance would be highly appreciated. Thank you in advance.
Excel VersionWindows Version
You may convert text to date by formula like
=DATE( RIGHT(A2,4), MONTH( 1&LEFT( RIGHT(A2, LEN(A2) -4), 3) ), LEFT( RIGHT(A2, LEN(A2) -8), 2))and apply to result any date format you wish.
14 Replies
You may convert text to date by formula like
=DATE( RIGHT(A2,4), MONTH( 1&LEFT( RIGHT(A2, LEN(A2) -4), 3) ), LEFT( RIGHT(A2, LEN(A2) -8), 2))and apply to result any date format you wish.
I am struggling with a similar date time string as text but it looks like
DATE TIME 2021-09-15 12:15 2021-09-15 12:30 2021-09-15 12:45 2021-09-15 13:00 2021-09-15 13:15 2021-09-15 13:30 2021-09-15 13:45 2021-09-15 14:00 The conversion string you wrote returned 1/15/1900 So it translated one part of it I think...
Are you sure you have texts, not actual datetime? You may check by entering =ISTEXT(A2) into empty cell.
- Thank you for the quick response Sergei. The formula you provided works perfectly!
nasirabd , you are welcome, glad it helped
Select the date column and apply the format as per picture below:
CTRL+1 (Shortcut to access it)
Thank you for your response. I did try using the custom date function in Format Cells and it did not make any changes.
