how can I display OS date format

Question

Level 1

4 points

how can I display OS date format

We typically use the mm/dd/yy or mm-dd-yy short date formats in our industry here in Canada. Ventura doesn't presently allow those options. We have several users of our Excel workbooks that require date entries and perform logic on dates, (i.e. how many days between xxxx and zzzz dates. The users have various versions of Macs with OS from High Sierra through Ventura.

Therefore, what I'm hoping to do is add a button to our workbook that shows the user the OS date format so they can enter dates according to THEIR machine setup.

Bottom line - I need some VBA that will retrieve and show the OS Region Date Format (preferably Short Date format)

Posted on Jan 10, 2023 5:35 PM

Reply

Answer 1

Barney-15E

Level 10

122,084 points

Jan 10, 2023 5:59 PM in response to Mentor3

Excel will recognize nearly any date format entered, even when you don't want it to. There is no need to enter it in any particular format. You can also display any date in any format you desire.

Is there some other reason you need to see it?

I couldn't find a way to show it.

Reply

Answer 2

Mentor3 Author

Level 1

4 points

Jan 10, 2023 6:30 PM in response to Barney-15E

As I noted, users are accustomed to entering mm/dd/yy. When you enter December 15th 2022 in the format 12/15/22. Yes, it will be recognized as a date, although some OS will determine it to be December 15 2022. Others like VENTURA interpret it to be 2012, 15th month, 22nd day, but stil display 12/15/22

When we try to run logic on that, such as =TODAY() minus that cell, we get #VALUE! because there's no 15th month. Therefore it would be best for the user to learn the format their machine is preferring as a default.

Also, we have seen instances when an OS update runs, the Region Date Format is changed back to yyyy/mm/dd even though the user had previously made a System Preferences change

Reply

Answer 3

Barney-15E

Level 10

122,084 points

Jan 10, 2023 6:43 PM in response to Mentor3

Excel should not misinterpret that regardless of the system date format. It could certainly misinterpret 1/5/25. That’s why data entry should always be done in ISO 8601 format, yyyy-mm-dd. There isn’t any chance of misinterpretation. Data entry should never be allowed to be arbitrary.

Reply

Answer 4

Barney-15E

Level 10

122,084 points

Jan 12, 2023 7:53 PM in response to Mentor3

I already told you I don’t know and don’t see how it is possible short of opening the System Settings. The format isn’t stored in a user accessible file unless you change it from the default. Correct data entry isn’t an opinion, unless you are fond of corrupt data. Does anyone actually depend on your data?

If you ask Microsoft, they might be able to provide some VBA for what Excel expects.

Reply

Answer 5

Mentor3 Author

Level 1

4 points

Jan 12, 2023 6:29 PM in response to Barney-15E

I appreciate your personal opinion. However, that doesn't resolve the need originally described.

"Bottom line - I need some VBA that will retrieve and show the OS Region Date Format"

Reply

how can I display OS date format

Similar questions