Converts UK -> US Dates

[GeorgeVince]

Hey,

I have a dataset of UK dates (DD/MM/YYYY), I've imported them and and converted them to (MM/DD/YYYY) using Range.NumberFormat = MM/DD/YYYY
When I open the dataset on my PC (UK), I see the dates in the MM/DD/YYYY format working as intended...

When I send the data to my client he still sees the dates as DD/MM/YYYY.

How can I bulk changes the dates from DD/MM/YYYY -> MM/DD/YYYY. I'm sure it's something simple but I just can't work it out!!

The only other option I can think of is changing looping through all dates and changing the using a split function:
Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2)

I have over 300 files, with 400k dates in each file. Surely there's a quicker way to do this??

[SamT]

Excel stores dates in cells as Doubles

The Cell NumberFormat merely tells Excel how to display the Double that represents a date.

Try this experiment:
In A1, enter = DATE()
In A2 enter, = NOW()
In B1 & B2 & B3, enter = A1 & =A2 & =A3

Format A1 & A2 as any Date pattern. Do not format A3. Format Column B as a Number with 15 decimal places.
Run this sub on the worksheet

Sub DateAsText()
Dim myDate As String
MyDate = "06/15/2017"
Range("A3").Text = MyDate
End Sub

Change the Date style of Column A. Watch A3 & Column B



Once you change a Date Formatted Numerical Cell value to a Text Value, you can never again work with it as a date until you convert it back to Numerical Date Values.

If your client wants USA style dates, he should format his workbook to Range.NumberFormat = MM/DD/YYYY

If there is no need to have actual Date values, then a simple
Range("A:A").Text = Range("A:A").Text
Should do it

[mdmackillop]

How are you sending the data? as a workbook, or are you exporting to another format?

[snb]

If your Excel file contains real dates you will be be shown dd/mm/yyyy, your colleague that is opening the same file in the US will be shown mm/dd/yyyy. So trust in any laziness.