Forum Discussion
Date Formatting
I have a data in which data is represented in the following format :
| 1st Aug 2021 | 8:57 pm |
I want to change this into 08/01/2021 8:57:00.
I have tried using text to columns, custom data format but no success. Can someone provide any inputs on how I can achieve this ?
Thank you.
If it's OK to use a formula to return a date/time value in another cell:
With a text value in A2, enter the following formula in B2:
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")Format B2 with your preferred date/time format, then fill down.
If you want to convert the value in the cell itself, it would require VBA.
4 Replies
If it's OK to use a formula to return a date/time value in another cell:
With a text value in A2, enter the following formula in B2:
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")Format B2 with your preferred date/time format, then fill down.
If you want to convert the value in the cell itself, it would require VBA.
- HansVogelaar
This worked 🙂
I am curious to know how is this working in the background ? Using substitute, I am able to remove the st,nd etc in my cell but how am I able to convert it into date format ? I was not able to do it before.SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")
removes the vertical line character "|" and the suffixes "st" etc.
In your example, this results in the text string "1 Aug 2021 8:57 pm". While it is still text, Excel can recognize it as date+time.
Applying - forces Excel to treat the string as a date+time, but it turns it negative. The second - turns it positive again.
In general, applying -- is a convenient way to convert a string that represents a date or number to a "real" date or number.
