Date Formats

[bayles]

Hi,

We have a spreadsheet that is being used by users in USA where standard date format is "mm/dd/yyyy" and also in Australia where it is "dd/mm/yyyy".

When we first open the spreadsheet before any macros are run the cell with the date in it is "Custom" format "dd/mm/yyyy".

The code below aims to change it to a date format but for the date 3rd June 2018 "03/06/2018" is being changed to "06/03/2018" in files opened by the US users.

Dim rng as Range

Set rng = Range(Sheets("DATA ENTRY").Cells(2, 3), Sheets("DATA ENTRY").Cells(3, 3)) ' CONVERT DATE IN DATA ENTRY FILE

rng.NumberFormat = "dd/mm/yyyy"
rng.FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1]),DAY(RC[-1]))"
Range(Sheets("DATA ENTRY").Cells(2, 2), Sheets("DATA ENTRY").Cells(3, 2)).Value = Range(Sheets("DATA ENTRY").Cells(2, 3), Sheets("DATA ENTRY").Cells(3, 3)).Value
rng.ClearContents

Does anyone know how to maintain "dd/mm/yyyy" format even on users computers that have "mm/dd/yyyy" as default?

 

[SpillerBD]

When going between these two systems use a format such as "D-MMM-YYYY"
The standard display format, with the asterisk, uses the local system formatting. So opening in USA or Australia, it correctly shows the LOCAL formatted date.
You can use a custom format or formatting that does not begin with an asterisk.

 

[bayles]

thanks for the advice SpillerBD.

So how would my code then be adjusted then?