Format Date

[Guest]

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

 

[Guest]

try:

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

 

[Guest]

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

 

[Guest]

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(xlConstants)
For Each rng In rngToSearch
rng.Value = ConvertToDate(rng.Value)
Next rng

End Sub