# Text Date format conversion

#### michaelg1040

##### New Member
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!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### michaelg1040

##### New Member
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
...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.

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

1,106,037
Messages
5,508,927
Members
408,701
Latest member
Ucchik7

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...