PDA

View Full Version : Solved: Covert dates into true dates



Shazam
02-16-2006, 02:20 PM
Need some with dates.

I have a worksheet that has dates but it starts off with
wk 02/13/2006
or
w 02/13/2006
or
week 02/13/2006
or
weeks 2/13/2006

and so on.

I recieve the worbook from the customer.

I have a simple macro that I recorded to a find and replace but the dates text is not always the same.

Is it possible a code that could recognize they are dates and convert them in true dates?

My sample workbook is below.

XLGibbs
02-16-2006, 02:49 PM
Sub FixDates()

Set rngDates = Sheets("Sheet1").Range(Cells(1, 4), Cells(1, Columns.Count).End(xlToLeft))

For Each c In rngDates
On Error Resume Next
c.Value = Right(c, Len(c) - WorksheetFunction.Find("/", c) + 2)
Next c

End Sub

Shazam
02-16-2006, 03:25 PM
Once again XLGibbs thank you very much. This is going to be a weekly report so the worksheet tab name will be change everytime to a new name. So I took one of your Ideas in your previous code that you provided to me and I modified it to be worksheet friendly. So thank you again I'm learning from you.



Sub FixDates()
With ActiveSheet
Set rngDates = Range(Cells(1, 4), Cells(1, Columns.Count).End(xlToLeft))

For Each c In rngDates
On Error Resume Next
c.Value = Right(c, Len(c) - WorksheetFunction.Find("/", c) + 2)
Next c
End With
End Sub

XLGibbs
02-16-2006, 04:10 PM
YOu still may want to have this syntax:


Set rngDates = .Range(.cells(1,4),cells(1,.columns.count).End(xlToLeft))


the . makes sure it is working on the object identified by the "With"