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

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.
--
- K Dales


"Alec" wrote:

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

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.



--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...
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.
--
- K Dales


"Alec" wrote:

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

if len(cell.Text) = 8 then

should be

if len(cell.Text) = 6 then

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.



--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...
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.
--
- K Dales


"Alec" wrote:

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

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.

"K Dales" wrote:

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.
--
- K Dales


"Alec" wrote:

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

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

--
HTH...

Jim Thomlinson


"Alec" wrote:

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.

"K Dales" wrote:

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.
--
- K Dales


"Alec" wrote:

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

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


On Wed, 28 Sep 2005 09:46:03 -0700, "Alec"
wrote:

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.

"K Dales" wrote:

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.
--
- K Dales


"Alec" wrote:

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

Thanks. You guys are great.

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Alec" wrote:

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.

"K Dales" wrote:

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.
--
- K Dales


"Alec" wrote:

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

Alec

This works for me.

Select the column then DataText to ColumnsNextNextColumn Data
FormatDateMDYFinish

Now Custom Format to YYMMDD


Gord Dibben Excel MVP

On Wed, 28 Sep 2005 09:21:06 -0700, "Alec"
wrote:

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