Why date is not corresponding to date format change?

[Maral_]

Dears

I have a list of dates in column A in a dd/mm/yyyy format, when I copy the same dates to column B and change the format to dd/mmm/yyyy some change but others don't?

I tried all possible tricks to unify the format but non worked.

Attached is an example

Kind regards
Maral

[Glenn Kennedy]

If you remove the centre formatting from Col A and widen it, you will see that some dates (non-working) are left justified and don't convert. These are text entries that look like dates. In B2, use =A2+0 and copy down.

[63falcondude]

It is because not all dates in column A are being seen as dates.

Are your regional settings dd/mm/yyyy or mm/dd/yyyy?

If your regional settings are dd/mm/yyyy, you should be able to do it like this:

Highlight column A > Data > Text to Columns > Finish

Now, all of your dates in column A should be recognized as dates.

[Maral_]

Thanks, Glenn

I can see that what you did have solved the problem on Col B. The question is, how can I fix the format of col A?

[Maral_]

Dear 63falcondude

I am not sure how, but your solution simply worked like a magic. I will try to dig behind the reason!
Many thanks
Maral

[63falcondude]

Thanks for the rep! Happy to help.