Text Date format conversion

michaelg1040

New Member
Joined
Jun 23, 2006
Messages
44
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

michaelg1040

New Member
Joined
Jun 23, 2006
Messages
44
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

Board Regular
Joined
Dec 14, 2009
Messages
54
...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....
Code:
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:
Code:
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
 
Last edited:

michaelg1040

New Member
Joined
Jun 23, 2006
Messages
44
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows
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

Board Regular
Joined
Dec 14, 2009
Messages
54
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) :
Code:
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.

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,810
Messages
5,483,042
Members
407,375
Latest member
achusp

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top