How to set default Date Format for all O365 users?

Question

One of users reported that his colleagues' Excel default date format is US, and some are UK, which causes their Excel files to cause errors when colleagues in different US/UK formats edit the same Excel file.

The picture show that when colleagues in UK format edit the excel file will occur the error due to the formula use US Date format.

So, how to set all user Excel Default Date Format to US? Thank you!

Answer 1

You can get date value using this formula.

=DATE(C2,D2,E2)

Answer 2

There is no such option for admin to force all the user using same date format.

Based on your description, you may add some columns to put year, month ,day separately. Then you formula will work no matter date format is used for users.

Answer 3

Hi Snow,

Now I have tried to set the format of the date column to be the TEXT field, need to manually entry the date, but the formula still retrieves the date format of the user's regional format, not retrieves the TEXT, why?

Thank you!

Answer 4

It could be someone copied from another cell which covered the cell format.

You may set data validation on it to only allow text date.

=istext(cellxxx)

Have you tried using three column to put year month day separately?

Answer 5

I found that the DATEVALUE function does not support US date format MM-DD-YYYY, so occur the #value!

Using three columns how to get the DATEVALUE?