1

I created a project management tool in Excel to be used by the team on Sharepoint. In a table it is required to insert the date when a ticket was open, closed, etc.

The problem with the date format is that in my machine (Office 2016) all dates appear as dd-mm-yyyy but to my colleagues (Office 2010) the dates inserted by me look okay but when they try to insert new ones it will appear as mm-dd-yyyy despite the fact that the formatted cells say "Locale: English (United Kingdom)" and dd-mm-yyyy.

The region and language settings are the same for all users and there is no code running in the workbooks. How can I fix this?

Thanks in advance.

Filipe Monteiro
  • 19
  • 3
  • VBA uses US pattern as default, so you can use the dates as String (`CStr()`) and then format as date on the spreadsheet. Because if the date is added directly from VBA, it will automatically reverse lots of times. A code can be used to [check if the dates are correct](https://stackoverflow.com/q/46454663/7690982) – danieltakeshi Oct 16 '17 at 16:18
  • Thank you for commenting. The dates are inserted manually and from my machine they all look okay. But from my colleague's machines, when trying to insert a date in the dd-mm-yyyy format it'll automatically change to mm-dd-yyyy, despite all settings being set to English (United Kingdom). – Filipe Monteiro Oct 16 '17 at 16:24
  • So i suggest that it is stored as Text String on the Sheet and you later convert to date with `Date()` formula and [mid,left,right](https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/) formulas. So you can make some calculations. Or with VBA's `CDate()` – danieltakeshi Oct 16 '17 at 16:53

0 Answers0