Change Date format to Hyphen while copy pasting data from CSV file to Excel

[Junarkar]

Hi,

I copy and paste a chunk of data into excel from CSV file which include a column for "Date" also.

The date format in CSV file is separated with a dot, like 27.04.2020. But I want to format it with a hyphen, like 27-04-2020.

Tried Format Cell option but its not giving result. I am doing this manually every time. Even though it wont consume much of my time, I'm curious to know if there is any way to automate this with the help of VBA. Something like, I have pasted the data to excel file and moved to another worksheet which trigger VBA and change the date format.

Thanks

 

[vletm]

Junarkar
If You need to copy ... then paste only visible values.

Hint: Have You tried to use other ways to get data from CSV-files?

 

[Marc L]

But I want to format it with a hyphen, like 27-04-2020

Hi,​

​

you can use the Excel Import Wizard and define the correct date setup for the column​

or after a 'bad' import you can use the Excel feature Text to Columns to correct the date column.​

​

Whatever the easy method used, dates appear with / as separator.​

If really the dates must be displayed with an hyphen as separator, you can just format the column​

or use the Excel Search & Replace method.​

​

Once manually it works, as it's so easy to activate the Macro Recorder and to redo the same operations :​

you will get your own code base …​

​

If you need more help, a reminder of how the initial post should be :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Junarkar]

Junarkar
If You need to copy ... then paste only visible values.

Hint: Have You tried to use other ways to get data from CSV-files?

Thanks, As of now I do not have CSV file with me for which I need to wait untill gov lifts COVID lock down so that I can access official reports.

I have tried text to column method while pasting data from CSV but no joy.

 

[vletm]

Junarkar
If You do not have any Your CSV-file now - it's challenge to solve Your case.
After - You have even one Your CSV-file ...
1) Open Your Excel
2) File > Import
... and follow instructions

 

[Junarkar]

Hi,​

​

you can use the Excel Import Wizard and define the correct date setup for the column​

or after a 'bad' import you can use the Excel feature Text to Columns to correct the date column.​

​

Whatever the easy method used, dates appear with / as separator.​

If really the dates must be displayed with an hyphen as separator, you can just format the column​

or use the Excel Search & Replace method.​

​

Once manually it works, as it's so easy to activate the Macro Recorder and to redo the same operations :​

you will get your own code base …​

​

If you need more help, a reminder of how the initial post should be :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

I recorded macro and this is the code I got;

>>> use code - tags <<<

Sub Test()
'
' Test Macro
'
    ActiveCell.FormulaR1C1 = "3/20/2020"
    Range("A454").Select
End Sub

I don't know how to apply this in a range. I tried below codes in Worksheet Deactivate event but no joy. I tried only in cell A453 but actually I want to change format for the entire A column;

Private Sub Worksheet_Deactivate()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("MTD Sales")

'ws.Range("a453").NumberFormat = "dd-mm-yyyy" - not working
ws.Range("a453") = Application.WorksheetFunction.Substitute(a453, ".", "-") - not working

    'ws.Range("a453") = Application.WorksheetFunction.Replace(a453, ".", "-") - not working


End Sub

 

[p45cal]

You could do something a little different; after you've pasted the data, it might look like this:

I've selected just a few cells for the following macro to work on:

Sub blah()
Selection.TextToColumns Destination:=Selection, FieldInfo:=Array(1, 4)
Selection.NumberFormat = "dd-mm-yyyy"
End Sub

Once run it leaves you with:

which are real Excel dates.

 

[Junarkar]

You could do something a little different; after you've pasted the data, it might look like this:
View attachment 67513
I've selected just a few cells for the following macro to work on:

Sub blah()
Selection.TextToColumns Destination:=Selection, FieldInfo:=Array(1, 4)
Selection.NumberFormat = "dd-mm-yyyy"
End Sub

Once run it leaves you with:
View attachment 67514
which are real Excel dates.

Hi,

I tried this but it is not working, and sometimes while running it is inserting rows below my selection. Can you please help.

 

[p45cal]

I tried this but it is not working, and sometimes while running it is inserting rows below my selection. Can you please help.

only if you attach a workbook with this happening and you describe what you do.
You should only run the macro when a single column of cells is selected, it won't work on multiple columns at once.
At the moment, I don't see how it can be inserting new rows?!

 

[Marc L]

I have tried text to column method while pasting data from CSV but no joy.

If it's a real csv text file it works each time as it's just need to well setup it in particular for the column formatting …​

​