From MMDDYY to YYMMDD

[Guest]

Hey Guys,

I have a column of dates in a text format as MMDDYY. I need to turn it into
YYMMDD. I have so many rows that I dont see myself manually reformating each
row of data to the YYMMDD format.

How can I code this to do it automatically for the whole column?

Thanks for your help. It is always appreciated.

Alec

 

[Guest]

Select the entire column by clicking on the column header (where it has the
column labels A B C etc.) and then use the Format menu to change the format
for that entire column.

 

[Tom Ogilvy]

To supplement K Dales fine advice:

In VBA that would be

Sub FormatColumn()
ActiveCell.Entirecolumn.NumberFormat = "YYMMDD"
End Sub

this assumes your values are stored as dates and formatting determines how
they appear.

However you use of the term "text format" is troubling. If they are really
text entries

Sub AlterColumn()
Dim cell as Range
Dim sStr as String, sStr1 as String
for each cell in selection
if len(cell.Text) = 8 then
sStr = cell.Text
sStr1 = Right(sStr,2) & Left(sStr,2) & Mid(sStr,2,2)
cell.Value = "'" & sStr1
end if
Next
End Sub

Select the cells to "format" and run the macro.

 

[Tom Ogilvy]

if len(cell.Text) = 8 then

should be

if len(cell.Text) = 6 then

 

[Guest]

This does not work. The format option is worthless in that case. This is a
text format and not even DateValue function changes my data.

Do you guys have a VBA coding for this?

Thanks.

 

[Guest]

So just to be clear you have a bunch of text values not a bunch of date
values. If that is the case then

Public Function ChangeTextDate(ByVal rngDate As Range) As String
Dim strDay As String
Dim strMonth As String
Dim strYear As String

strDay = Mid(rngDate.Value, 3, 2)
strMonth = Left(rngDate.Value, 2)
strYear = Mid(rngDate.Value, 5, 2)

ChangeTextDate = strYear & strMonth & strDay
End Function

Sub test()
Dim rngToChange As Range
Dim wks As Worksheet
Dim rngCurrent As Range

Set wks = ActiveSheet
Set rngToChange = Intersect(wks.UsedRange, wks.Columns("B"))

For Each rngCurrent In rngToChange
If Len(rngCurrent.Value) = 6 Then _
rngCurrent.Value = ChangeTextDate(rngCurrent)
Next rngCurrent
End Sub

 

[Myrna Larson]

The problem is, you said you had "dates". A date in Excel is the number of
elapsed days since Dec 31, 1899. Today, Sep 28 2005, is the number 38623, not
the number 92805. To convert your numbers to true dates, you can use Data/Text
to Columns. Select the column of "dates", and at the 3rd dialog box specify
that the column is a date, format mdy.

Or you can use a "helper column" with a formula such as

=DATE(MOD(A1,100)+2000,INT(A1/10000),MOD(INT(A1/100),100))

 

[Guest]

Thanks. You guys are great.

So just to be clear you have a bunch of text values not a bunch of date
values. If that is the case then

Public Function ChangeTextDate(ByVal rngDate As Range) As String
Dim strDay As String
Dim strMonth As String
Dim strYear As String

strDay = Mid(rngDate.Value, 3, 2)
strMonth = Left(rngDate.Value, 2)
strYear = Mid(rngDate.Value, 5, 2)

ChangeTextDate = strYear & strMonth & strDay
End Function

Sub test()
Dim rngToChange As Range
Dim wks As Worksheet
Dim rngCurrent As Range

Set wks = ActiveSheet
Set rngToChange = Intersect(wks.UsedRange, wks.Columns("B"))

For Each rngCurrent In rngToChange
If Len(rngCurrent.Value) = 6 Then _
rngCurrent.Value = ChangeTextDate(rngCurrent)
Next rngCurrent
End Sub

 

[Gord Dibben]

Alec

This works for me.

Select the column then Data>Text to Columns>Next>Next>Column Data
Format>Date>MDY>Finish

Now Custom Format to YYMMDD


Gord Dibben Excel MVP