different date representation of the same excel

Occasional Visitor

Situation is as follows:
We have a report in excel. When a colleague opens a report, he gets the date format DD/MM/YYYY (29-09-2022). If I or another colleague opens the same excel, we get MM/DD/YYYY (09-29-2022). Since we share international excel documents, it is important that the date is always displayed in the same way. It seems as if the system controls certain settings. Does anyone have any idea how we can fix this? We've never had a problem here before.

1 Reply

@ursulaR 

If you select a cell with a date and press Ctrl+1 to activate the Format Cells dialog, you will see that there are two date formats in the list of pre-defined formats that have an asterisk:

S1792.png

Those two formats are the Short Date and Long Date formats defined in the user's system settings. So using the one at the top, you would see 09-29-2022, your colleague would see 29-09-2022, and I would see 2022-09-29.

This is actually a good idea: if you enter 09-05-2022, you mean the 5th of September, but your colleague would interpret it as the 9th of May.

To avoid this problem, select another format than the Short Date format, or select Custom and create a custom format. Preferably, use an unambiguous format such as dd-mmm-yyyy (29-Sep-2022).