Forum Discussion
MO 365 Excel - Difficulty Changing a Date format
I am using MO 365 - apps for Business, currently received an Excel Spreadsheet and am attempting to change date format in Excel and have tried all the usual methods however the dates remain unchange...
m_tarler has made a very good point. If standard date formatting isn't working, the values are likely stored as text, not as actual dates.
m_tarler’s suggested method — Text to Columns — is typically the most efficient fix:
- Select the date column.
- Go to Data > Text to Columns.
- Choose Delimited > Next > Uncheck all delimiters > Next.
- Under Column data format, select Date: MDY > Finish.
This converts the text to real date values. After that, you can format the cells as needed:
- Right-click > Format Cells > Custom > Type: yyyy/m/d
That will give you 2025/1/31.
If for some reason Text to Columns doesn't work, you can also use a formula in a helper column:
=DATE(RIGHT(A1,4), LEFT(A1,FIND("/",A1)-1), MID(A1,FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)-FIND("/",A1)-1))
But m_tarler’s approach is simpler and should resolve the issue in most cases.
My answers are voluntary and without guarantee!
Hope this will help you too.