How to convert dates marked as General to actual dates?

[timcheng616]

I have a long list of dates formatted with zeros in front of the month. So Pivot tables doesn't register them as a date. So I'd like to change the format (i.e. from 02/14/2022 to 2/14/2022). I did try to go into formatting and change it from "general" to "date." But nothing changes

the only way it seems to register it as a date is if I go into the cell move my cursor to the end and then hit "Enter" then it converts to a date. I don't want to do this manually for every single one. Is there a way for me to convert this to dates?

Thanks.

 

[Joe4]

Welcome to the Board!

It sounds like those date entries are actually Text entries, which is why changing the format does not change how they look.
You can change them all at once by selecting the column, going to the Data menu, selecting "Text to Columns", and then clicking "Finish".
That will "re-enter" all the values in that column at once, which should fix it.

 

[Fluff]

Select the column, then on the data tab select Text to columns, Delimited, next, clear all checkboxes, next, select MDY from the date dropdown, Finish

 

[timcheng616]

Welcome to the Board!

It sounds like those date entries are actually Text entries, which is why changing the format does not change how they look.
You can change them all at once by selecting the column, going to the Data menu, selecting "Text to Columns", and then clicking "Finish".
That will "re-enter" all the values in that column at once, which should fix it.

That worked thanks!

 

[Joe4]

You are welcome.
Glad we were able to help!