Excel Online Random Date Format Changes

[BrainHurts]

I made a spreadsheet offline in 16.43 version excel for mac. It all worked fine. When I upload to excelonline and try to set the the dates column to UK date format it won't stay set. Keeps changing to US date format, or Azerbaijani Cyrillic. I'm going mad because I need the dates column to work. 

Help desperately needed and gratefully received  

[#2]

@BrainHurts 

Excel Online keeps formats based on locale set for OneDrive/SharePoint where the file is kept (or within account for personal license)

[#3]

Thanks @Sergei Baklan 

It now seems to be the case that if I enter the date in the US format it will change it to the UK format and present correctly. But if I enter it as the UK format it won't recognise it properly? So confused??

Can you change the locale setting at the source where the file is kept / our account for the licence?

[#4]

@BrainHurts 

That since you most probably have US locale for the OneDrive/SharePoint, usually that's default one. Actually dates in Excel are integer numbers starting from 1 which is 01 Jan, 1900. Date format is only human friendly representation of such numbers.

If you have US locale and enter the date in US format, Excel understands you try to enter the date (it compare the value with locale pattern for the date) and converts it correctly to date (aka integer number). You may apply any built-in or custom format to that date, for example ISO one as yyyy-mm-dd.

If you have US locale and try to enter the date in another locale format (e.g. UK one), Excel won't recognize such value as the date (thus doesn't convert it internally into related integer) and takes such value just as text.