Imported Dates cannot be re-formatted

[ivath0]

I've struggled with this issue for some months now. I have a workaround but it is not useable in every application.
In the attached list, imported into Excel (Office 365) from a CRM, the format cannot be changed and the dates cannot be used in any date related formulae.
Both the CRM and Excel have Locale set to English (United Kingdom).
I really need to understand the cause of this formatting issue in order to have the opportunity of an absolute solution.

My workaround is to apply 'Text to Columns' and set format to date.

[WideBoyDixon]

You have two choices I guess:

Create a formula in column K (for example) like this in K7:

Please Login or Register  to view this content.

You can then copy that down and then copy the values into Column A although you'd then need to set the number format to Date. You can then clear the contents of column K.

Alternatively, you could use a macro like this:

Please Login or Register  to view this content.

WBD

[kvsrinivasamurthy]

Select the range of date A7:A28.
Data -->Text to Columns
Delimited --> Next --> Next
Select Date --> DMY --> Finish

[ivath0]

Thank you WideBoyDixon, That's a better, simpler, alternative to my workaround. I can include the formula in the template.