I have a column with the data type as general - This column should be a date

Sample data
20051231
20051230
20051229
20051228
20051227

I would like to write a rutine that would convert the date to

12/31/2005
12/30/2005
12/29/2005
12/28/2005
12/27/2005

Please I need help!!!!!!

thank you in advance

try:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
--
Gary''s Student


"Chris" wrote:
I have a column with the data type as general - This column should be a date

Sample data
20051231
20051230
20051229
20051228
20051227

I would like to write a rutine that would convert the date to

12/31/2005
12/30/2005
12/29/2005
12/28/2005
12/27/2005

Please I need help!!!!!!

thank you in advance

Hi,
From the date format I am assuming you US-based. Try:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Create new column following your date column, copy in the formula with
apprpriate column change and copy down.

Edit==Copy the column then Edit==Paste Special ==Values on same column.
If OK, delete source column.

As I am UK-based I used the following to get your format:

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"MM/DD/YYYY")


TEST first!


HTH
"Chris" wrote:

I have a column with the data type as general - This column should be a date

Sample data
20051231
20051230
20051229
20051228
20051227

I would like to write a rutine that would convert the date to

12/31/2005
12/30/2005
12/29/2005
12/28/2005
12/27/2005

Please I need help!!!!!!

thank you in advance

Give this a try... It will only overwrite values, not formulas.

Public Function ConvertToDate(ByVal DateNumber As Long) As Date
ConvertToDate = DateSerial(CInt(Left(DateNumber, 4)), _
CInt(Mid(DateNumber, 5, 2)), CInt(Right(DateNumber, 2)))
End Function

Sub test()
Dim rngToSearch As Range
Dim rng As Range

Set rngToSearch = Sheets("Sheet1").Columns("A").SpecialCells(xlConst ants)
For Each rng In rngToSearch
rng.Value = ConvertToDate(rng.Value)
Next rng

End Sub
--
HTH...

Jim Thomlinson


"Chris" wrote:

I have a column with the data type as general - This column should be a date

Sample data
20051231
20051230
20051229
20051228
20051227

I would like to write a rutine that would convert the date to

12/31/2005
12/30/2005
12/29/2005
12/28/2005
12/27/2005

Please I need help!!!!!!

thank you in advance