Forum Discussion
Solved
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, we...
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.
Are you sure you have texts, not actual datetime? You may check by entering =ISTEXT(A2) into empty cell.
I have it as a csv txt file and I cannot get this date time stamp string to come in as a number. I can get the data but the time comes as 1215 instead of 12:15
- Apologies for the resend. I don't know the user interface here that let me get to your answer. The simpler version works. I'm curious to try the string formula.
Thank you very much. Awkwardly dumb issue but with a lot of data, vital to have an easy way to convert.There is another way which could work
- Enter this date in any empty cell in your regional format
- Copy this cell
- Select all dates in one of the column
- Paste -> Paste Special -> Multiply -> Ok
- Now we have dates (aligned to the right), not texts