Excel date format

[rikkiw]

Hello!

I have date column with different date format, even I use Number format however the date just didn't change, example below. How can I format them to be the same date like mm/dd/yyyy or whatever? Thank you!!!!

MW Request Submitted 5/31/2018 8/13/2019 6/25/2018 5/31/2018 7/9/2018 2018-Jun-01 2018-Jun-01 6/15/2018 6/20/2018 6/19/2018 6/19/2018 6/19/2018 6/19/2018 2018-Jun-19 2018-Jun-20 6/27/2018 7/18/2018 7/18/2018 11/27/2018 2018-Aug-03 8/20/2018 8/20/2018 11/5/2018 8/30/2018

 

[Cubist]

Are they date data type or text?

 

[rikkiw]

Are they date data type or text?

I already format them as Short Date, so I assume should be date type, however the "2018-Jun-20" dates look like text, even it shows Date when I click the cell... quite confusing

 

[Cubist]

I already format them as Short Date, so I assume should be date type, however the "2018-Jun-20" dates look like text, even it shows Date when I click the cell... quite confusing

A way to check is =ISNUMBER(A1).

See if it returns TRUE or FALSE. If TRUE then it is date, else it's text. Let us know.

 

[rikkiw]

A way to check is =ISNUMBER(A1).

See if it returns TRUE or FALSE. If TRUE then it is date, else it's text. Let us know.

Thank you!! just tried, all the date with month name in the middle, they show as FALSE, so they are actually text ...

 

[Cubist]

Try this:

1. Select the cells.
2. Go to the "Data" tab on the Excel ribbon.
3. Click on "Text to Columns" in the "Data Tools" group.
4. In the "Convert Text to Columns Wizard," choose "Delimited" and click "Next."
5. Unselect all delimiter options (tabs, commas, etc.) and click "Next."
6. In the "Column data format" section, choose "Date" and then select the format "YMD" (Year, Month, Day).
7. Click "Finish" to convert the text string into a date format.

 

[rikkiw]

Try this:

1. Select the cells.
2. Go to the "Data" tab on the Excel ribbon.
3. Click on "Text to Columns" in the "Data Tools" group.
4. In the "Convert Text to Columns Wizard," choose "Delimited" and click "Next."
5. Unselect all delimiter options (tabs, commas, etc.) and click "Next."
6. In the "Column data format" section, choose "Date" and then select the format "YMD" (Year, Month, Day).
7. Click "Finish" to convert the text string into a date format.

Woo that's MAGIC!!! thank you so much it just got resolved!!! Wish you a great weekend!!!

 

[Cubist]

You're welcome. Cheers.