Excel/Text to date
QUESTION: hey Jan,
Every day I copy and paste information (provided by another platform) into an excel as text into my spreadsheet. One of these columns contains a date in this format: dd.mm.yyyy so I recorded a macro to change this to an excel date: yyyy/mm/dd (I need this in a date format to perform some calculations)
In my macro I use the "text to column" to perform this operation. I selected A1:A5000 (because every day this spreadsheet grows) All this works well.
But here is the problem----- say today I have in A1:10 dates like this (already converted to date):
Now I click my macro button again. It not find dd.mm.yyyy so for some reason it change the current dates to:
How can I overcome this?
If have date formats converted today and tomorrow I copy and paste again then I have mixture of dates (which is already converted the previous day) and text (newly added today) Please see example below:
So I only want the last four entries converted. Also I want to make provision for 5000 rows in my sheet.
How can I overcome this?
ANSWER: Can you please share your macro code?
---------- FOLLOW-UP ----------
QUESTION: Hi Jan,
Here is the code. You will see if you click on it more than once every time it change the dates. This macro take dates (in dd.mm.yyyy format - please note the "dots") in column G and convert it to excel dates (yyyy/mm/dd).
I downloaded an add-in for excel named "Kutools" (30 day trial) this add-in can take any and ANY non-standard date in a whole column and convert it to dates with a click of a button. It has some other functionalities too, but I do not wish to pay for something just to be able to do one thing...
See here: https://www.extendoffice.com/product/kutools-for-excel/excel-convert-text-to-date.html
Here is my macro that I recorded:
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
This problem is due to the fact that VBA speaks American and tries to convert dates to the US MDY format, regardless of your text-to-columns settings. Instead, try this little macro:
Dim oCell As Range
Dim vVal As Variant
For Each oCell In Intersect(ActiveSheet.UsedRange, Range("G:G")).SpecialCells(xlCellTypeConstants, xlTextValues)
vVal = Split(oCell.Value, ".")
If vVal Like "*.*.*" Then
oCell.Value = DateSerial(vVal(2), vVal(1), vVal(0))