Forum Discussion
Date Formatting
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.
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.
- HansVogelaar Understood.
Thank you so much for all the help!