Excel/Text to date

Advertisement


Question
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):

2016/01/01
2016/01/02
2016/01/03
2016/01/04
2016/01/05
2016/01/06
2016/01/07
2016/01/08
2016/01/09
2016/01/10

Now I click my macro button again. It not find dd.mm.yyyy so for some reason it change the current dates to:


2016/01/01
2016/02/01
2016/03/01
2016/04/01
2016/05/01
2016/06/01
2016/07/01
2016/08/01
2016/09/01
2016/10/01
2016/11/01
2016/12/01

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:

2016/01/01
2016/01/02
2016/01/03
2016/01/04
2016/01/05
2016/01/06
2016/01/07
2016/01/08
2016/01/09
2016/01/10
11.01.2016
12.01.2016
13.01.2016
14.01.2016

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?

Thanks!!

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:

Sub Button1_Click()

   Range("G1:G500").Select
   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
End Sub


Thanks,
Michelle

Answer
Hi Michelle,

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:

Sub ConvertTextDatesToRealDates()
   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))
       End If
   Next
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.