Dates & VB

[Theresa]

Hi:

I receive a file that contains a column of dates in a format that is not
recognized as a date: ie: 20080714. I am trying to create a macro to
insert the "/" at the required places.

Here is the code I am using:

Range("D3" & Range("E65536").End(xlUp).Row).FormulaR1C1 = Trim(Left(C3, 4)
& "/" & Mid(C3, 5, 2) & "/" & Right(C3, 2))

I want to look at the data in Column E to see how far to go, then take the
date in Column C and put it in Column D in the correct format. When I run
the macro, all I get is // with nothing else.

What am I doing wrong?

Thanks,

 

[Gary''s Student]

With 20080714 in cell C3, this macro will put the date in C4:

Sub theresa()
v = Range("C3").Value
yr = Left(v, 4)
mn = Mid(v, 5, 2)
dy = Right(v, 2)
Range("C4").Value = DateSerial(yr, mn, dy)
End Sub

 

[Theresa]

Hi Gary:

But I need it to evaluate how many rows to go down, and the dates will be
different in each row. EG: I have dates in rows C3:C30 in the 20080714
format. I need the dates to be in the 2008/07/14 format. The number of rows
will change constantly so I need to evaluate how many rows to work with.

Here is a small sample file:

Carrier Pro/Inv Date Date2 Customer
DRR CLG1510679 20080714 Gescan
DRR DAR2639057 20080714 Gescan
DRR MTL4337916 20080714 Gescan
DRR CLG1510680 20080714 Gescan
DRR DAR2639058 20080714 Gescan
DRR MTL4337917 20080714 Gescan
DRR CLG1510681 20080714 Gescan
DRR DAR2639059 20080714 Gescan
DRR MTL4337918 20080714 Gescan
DRR CLG1510682 20080714 Gescan
DRR DAR2639060 20080714 Gescan
DRR MTL4337919 20080714 Gescan
DRR CLG1510683 20080714 Gescan
DRR DAR2639061 20080714 Gescan
DRR MTL4337920 20080714 Gescan


Date2 is blank and this is where I want to show 2008/17/14.

Thanks,

 

[Office_Novice]

This Worked for me:

Option Explicit
Sub FixDates()
Dim YourRange As Range
Dim ws As Worksheet
Dim x As Integer
Dim i As Variant

Set ws = ActiveWorkbook.Worksheets(1)
Set YourRange = ws.Range("C:C")
x = 1

For Each i In YourRange
ws.Cells(x, 4).Value = Left(i, 4) & "/" & Mid(i, 5, 2) & "/" & Right(i, 2)
x = x + 1
Next
End Sub

Good luck.

 

[Office_Novice]

After reading your response try this:

Option Explicit
Sub FixDates()
Dim YourRange As Range
Dim ws As Worksheet
Dim lastrow
Dim x As Integer
Dim i As Variant

lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set ws = ActiveWorkbook.Worksheets(1)
Set YourRange = ws.Range("C3:C" & lastrow)
x = 3

For Each i In YourRange
ws.Cells(x, 4).Value = Left(i, 4) & "/" & Mid(i, 5, 2) & "/" & Right(i, 2)
x = x + 1
Next
End Sub

 

[Bernie Deitrick]

Theresa,

Try this, which will create actual dates - this also assumes that your default date system is US...
It will take just a little rearrangement to do Euro style dates.

Sub TheresaTryNow()
With Range("D3" & Range("E65536").End(xlUp).Row)
.Formula = "=DATEVALUE(Mid(C3, 5, 2) & ""/"" & Right(C3, 2)& ""/"" & Trim(Left(C3, 4)))"
.NumberFormat = "mmmm dd, yyyy"
End With
End Sub


HTH,
Bernie
MS Excel MVP

 

[Theresa]

Thanks, I just changed the date format and it works great!

 

[Dave Peterson]

Another way (manually) is to select the range and then use data|text to
columns. Fixed width, the field is a date in ymd order.

In code:

with activesheet
with .Range("C3:C" & .cells(.rows.count,"E").End(xlUp).Row)
.texttoColumns Destination:=.cells(1).offset(2,1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 5)
end with
end with

But I would have thought that you would use column C to get the lastrow. And
that you'd start in D3, not D5.

You may not need this, but it uses column C and then plops the result right back
where it found the data.

with activesheet
with .Range("C3:C" & .cells(.rows.count,"C").End(xlUp).Row)
.texttoColumns Destination:=.cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 5)
end with
end with