Issue with date formatting

[King Mustard]

Please see the attached workbook, that contains a list of UK-style dates.

I want all of the rows to be of format Long Date but when I select the column and change it, only some change (the rest show Short Date).

Can anyone help explain why?

[jomaor1]

It's because you have them as "Text".
You can prove this by after Selecting "Long Date" and double clicking on the "wrong" cells + Enter,
that it corrects the format.

Cheers!

[King Mustard]

It's because you have them as "Text".
You can prove this by after Selecting "Long Date" and double clicking on the "wrong" cells + Enter,
that it corrects the format.

Cheers!

The thing is, I have selected the whole column, changed it to something else (such as Percentage) then back to Long Date but some are still wrong.

Shouldn't changing them to Percentage have reset them all to the same format (in this case, Percentage)? Do I need to double-click on each one?

[jomaor1]

you can also use the "NUMBERVALUE" function, and then change to Long Date
like in the attachment

Cheers!

[FDibbins]

The thing is, I have selected the whole column, changed it to something else (such as Percentage) then back to Long Date but some are still wrong.

Shouldn't changing them to Percentage have reset them all to the same format (in this case, Percentage)? Do I need to double-click on each one?

If you have a "date" formatted as text, changing the format will not change anything - all formatting does (other than text) is change the cosmetic appearance of the cell, not the underlying contents.

[King Mustard]

If you have a "date" formatted as text, changing the format will not change anything - all formatting does (other than text) is change the cosmetic appearance of the cell, not the underlying contents.

Surely if I select all of column A (which contain only dates) and change the cell type to Short Date, Excel should make them no longer be formatted as Text but as Short Dates?

However, this does not happen until, as jomaor1 said, I double-click in each cell and press Enter.

Sounds like a bug to me.

[FDibbins]

Nope, not a bug, it's just how excel works...like I said, formatting does not change underlying cell contents, only the cells appearance.