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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,239
Messages
5,509,994
Members
408,768
Latest member
ndg4405

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...
Top