Text Date format conversion

[michaelg1040]

I'm having problems with some date formats on a spreadsheet which I'm hoping you can help with. This spreadsheet is our company orderbook which is pulled from a central database and for some reason some of the dates come out as text format. I have a macro which formats the spreadsheet so that it is a bit more user friendly but I'm struggling to get all the dates in the same format.
This is what I'm doing so far using the macro - I insert a column next to the date column, then get the macro to look in the date column and convert any text dates or if a date is already in the correct format to just copy it across. It converts the text dates without any problem but for some reason it's not copying across the dates that are already in the correct format. The plan is once all the dates are in column Z I can then get rid of column Y which has all the incorrectly formatted dates.
Here is the code:

For counter = 2 To Lastrow
If Range("Y" & counter).NumberFormat = "DD/MM/YYYY" Then Range("Z" & counter).Value = _
Range("Y" & counter).Value Else Range("Z" & counter).FormulaR1C1 = "=DATEVALUE(RC[-1])"
Next counter

I'm sure I'm missing something really simple but I can't figure it out at all!

 

[michaelg1040]

I've just managed to figure this one out myself so if anyone has a similar problem this is how I did it:

For counter = 2 To Lastrow
Range("Z" & counter).FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-1]),RC[-1])"
Next counter

 

[boim]

...The plan is once all the dates are in column Z I can then get rid of column Y which has all the incorrectly formatted dates....

For counter = 2 To Lastrow
Range("Z" & counter).FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-1]),RC[-1])"
Next counter

Your column Z has formula that refers to a deleted column. I'm pretty sure that won't work. You've got to paste special, value only to another column, and that loop is not necessary.

Try this instead:

dim myWks as Worksheet
dim rTemp as Range
dim rDest as Range

set myWks = Worksheets("Your worksheet name")
set rTemp=myWks.Range("Z2:Z" & Lastrow)
set rDest=myWks.Range("Y2:Y" & Lastrow)

' Copy correct dates back to original column and delete temporary column.
With rTemp
   .FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-1]),RC[-1])"
   .Copy
   rDest.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  .Clear
End With

 

[michaelg1040]

Thanks for that but maybe I should have been clearer when I posted my code. I only posted the code that deals with the datevalue, I already have the code for the copy/paste values on the formula column, I just didn't post it.
Thanks anyway though

 

[Scott Huish]

You missed the part of boim's post about not needing the loop, you don't need it:

Range("Z2:Z" & counter).FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-1]),RC[-1])"

 

[boim]

I was having the same problem of converting multiple text columns to their proper date and time format when I read your post and then replied with the solution above. Not wanting to use for loop (VBA.Datevalue can't accept a range argument) :

For Each c In Sheets(1).ListObjects(1).ListColumns(colDate).DataBodyRange
   c = DateValue(c)
Next

nor liking the copy and paste method, I try another solution below that utilizes Excel Datevalue and IFError over a range. It's rough but I hope it gives someone an idea with some modification where necessary.

My data is formated as a table.

Dim tb as ListObject
Dim lc as ListColumn

Set tb=Sheets(1).ListObjects(1)

For Each lc In tb.ListColumns
   Select Case lc.Name
      Case colDate
         ' Add temp column
         tb.ListColumns.Add lc.Index + 1
         tb.ListColumns(lc.Index + 1).DataBodyRange.NumberFormat = "mm/dd/yyyy;@"
         tb.ListColumns(lc.Index + 1).DataBodyRange.FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-1]),"""")"
         ' Copy back
         lc.DataBodyRange = tb.ListColumns(lc.Index + 1).DataBodyRange.Value
         ' Remove temp column
         tb.ListColumns(lc.Index + 1).Delete

      Case another date/time column name etc.
         etc...

   End Select
Next

The code "might" be slow (not tested) over a "large" range. It could just add the temp column at the end of the columns but then need to calculate to determine the relative position for the FormulaR1C1.