VBA changing UK Date format to American

[mazzz]

Hi,

I have a macro which pastes a table of data from a 3rd party application. The first row of the table is date format (dd/mm/yyyy).

When I run the code the data is pasted however the date format is changed to American (mm/dd/yyyy). Is there a way to retain the UK date format when running the paste macro?

Simply this is my code:

Range("Paste_Cell").PasteSpecial xlPasteAll

Would appreciate any help with this. I've seen some answers regarding changing the region settings in control panel. I can confirm, my region settings are set to Engilsh(United Kingdom).

 

[mazzz]

Right okay, so I've found a solution that works, its a bit unordinary but it works.

So solution 1:
' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

Dim DateRange As Range

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell in DateRange
Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

This works but is incredibly slow as it cycles through every cell in the range and re-formats, especially as I have near to 3000 rows of data.

The better solution is along the lines of what Alex suggested. I had tried that before but annoyingly, when you do the text-to-columns manually/ record the macro it works like a charm. However, when you apply the macro it doesn't work, it just returns the same American format (mm/dd/yyyy). I think it's because VBA outputs mm/dd/yyyy so when you specify MDY with "FieldInfo:=Array(1, 3)" it doesn't change it.

Effectively this is what is happening:

Source Data (dd/mm/yyyy) -> VBA Paste converts to (mm/dd/yyyy) -> Text-to-columns recognises (mm/dd/yyyy) so VBA doesn't change it as it outputs in mm/dd/yyyy.

However, this variation also works:

Range("Paste_Cell").PasteSpecial xlPasteAll
' Set date format to dd/mm/yyyy
Range("Range_Date").TextToColumns Destination:=Range("Range_Date"), DataType:=xlDelimited, _
FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True

With this, I specified that the pasted data was in dd/mm/yyyy format, as shown by the number 4 in array, when in fact the VBA pastes the data in mm/dd/yyyy format.

But because I've specified in the text-to-columns that the format is dd/mm/yyyy....VBA changes it to output mm/dd/yyyy which returns it back to the orginal format. In effect...

Source copied: 11/06/2023 (dd/mm/yyyy)
VBA Paste: 06/11/2023 (mm/dd/yyyy)....however as my cells are formatted to dd/mm/yyyy and regional settings are set to UK this reads 6th of November
Text-to-column macro: 11/06/2023 (dd/mm/yyyy)...VBA flips it again because i've declared that the pasted format is DMY instead of the true MDY

This solution takes almost no time compared to the first solution.

I hope this helps someone, and thank you for the help guys!

 

[mazzz]

You say that this works, but takes a long time:

' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

    Dim DateRange As Range

    Range("A1").PasteSpecial xlPasteAll
    Set DateRange = Range("A2:A3000")
    For Each Cell In DateRange
        Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
    Next Cell

If so, then you should be able to simplify to:

' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

    Dim DateRange As Range

    Range("A1").PasteSpecial xlPasteAll
    Set DateRange = Range("A2:A3000")
    DateRange.NumberFormat = "dd/mm/yyyy"

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

I did try ...DateRange.NumberFormat = "dd/mm/yyyy"...as you said...
but it didn't work

Also sorry, literally my first time posting on a forum, not to sure how to do the code tags??

 

[rlv01]

I did try ...DateRange.NumberFormat = "dd/mm/yyyy"...as you said...

It works for me. I suspect you may have missapplied it, so you would need to post your code showing how you used it.

Also sorry, literally my first time posting on a forum, not to sure how to do the code tags??

Did you click on the link I included in my post? It takes you to a web page that explains in detail how to do it.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[mazzz]

It works for me. I suspect you may have missapplied it, so you would need to post your code showing how you used it.


Did you click on the link I included in my post? It takes you to a web page that explains in detail how to do it.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

This is how I structured it when using that method:

Sub Paste()

' Date_Range is the nambed range which refrences A2:lastRow
' Date data is in A column with A1 being a heading "Date"

Dim DateRange As Range

Range("A1").PasteSpecial xlPasteAll

Set DateRange = Range("Date_Range")
DateRange.NumberFormat = "dd/mm/yyyy"

End Sub

The output is below...should be 11/06/2023

 

[rlv01]

You said earlier, that this worked:

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell in DateRange
   Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

Was that not correct? Because if that worked, then this should work too.

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
DateRange.NumberFormat = "dd/mm/yyyy"

Or are the dates being stored in col A as text values?

 

[mazzz]

You said earlier, that this worked:

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell in DateRange
   Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

Was that not correct? Because if that worked, then this should work too.

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
DateRange.NumberFormat = "dd/mm/yyyy"

Or are the dates being stored in col A as text values?

The dates aren’t being stored as text. The first version using Format( ) works. But the .NumberFormat version doesn’t. I agree it should work but it doesn’t work for me for some reason… has it worked for you?

 

[rlv01]

The dates aren’t being stored as text. The first version using Format( ) works. But the .NumberFormat version doesn’t. I agree it should work but it doesn’t work for me for some reason… has it worked for you?

Yes, but I don't have your data to experiment on.


FWIW, when you post an image of your data instead of something that can be copied and pasted into a spreadsheet, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool (link below) to post your data in a way that makes it accessible to others. this free tool instead to post some sample data.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[MARK858]

VBA flips it again because i've declared that the pasted format is DMY instead of the true MDY

You need to have the Text To Columns match the source data (not the outcome that you require), in this case MDY as Alex posted and then format the cell normally to how you want it to appear

 

[Akuini]

So solution 1:
' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

Dim DateRange As Range

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell in DateRange
Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

This works but is incredibly slow as it cycles through every cell in the range and re-formats, especially as I have near to 3000 rows of data.

This works but is incredibly slow

I tried your code: copy 3000 dates from a text editor the run the code in a clean sheet, it took only less than a second.
Perhaps your file has many formulas which are recalculated when you ran the code.
Try the code in a clean workbook, it should be fast.

To speed up the code in your actual workbook, try this:

Sub try()
Dim DateRange As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual


Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell In DateRange
Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic

End Sub