date formatting

[BernieH]

I have a *.CSV file with a column of dates in various formats, such as -

1/01/1900
1/07/1901
1/06/1903
1880
1901
1904
1911
1911
1913
1/05/2004
Winter 2004

I need to get these dates into YYYYMMDD or YYYY format (depending on how
much info is available, e.g. 1911 is OK left as 1911). Formatting the cells
as custom YYYYMMDD however doesn't work, as ?pre-1900 dates get converted
wrongly.

There are ~20K lines in the file, so any solution has to be pretty much
automatic.

Your help would be very much appreciated!

TIA

bernieh

 

[JE McGimpsey]

One way, using a macro. It will place the new values in the column to
the right of the originals:

Public Sub ConvertDates()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then
.Offset(0, 1).Value = Format(.Value, "yyyymmdd")
Else
.Offset(0, 1).Value = Right(.Text, 4)
End If
End With
Next rCell
End Sub

Note that this is based on the forms in your data.

If you have something like

01/30

meaning January of 1930, this macro will give an incorrect answer.

 

[Ron Rosenfeld]

One way, using a macro. It will place the new values in the column to
the right of the originals:

Public Sub ConvertDates()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then
.Offset(0, 1).Value = Format(.Value, "yyyymmdd")
Else
.Offset(0, 1).Value = Right(.Text, 4)
End If
End With
Next rCell
End Sub

Note that this is based on the forms in your data.

If you have something like

01/30

meaning January of 1930, this macro will give an incorrect answer.

Interesting output from your macro.

If the input date is between 1/1/1900-2/28/1900, the output from your macro
will be off by one date.

I imagine this has to do with the infamous year 1900 leap year issue.

In VBA, Day 1 is 12/31/1899 and the FORMAT function acts accordingly.

======================
1/1/1900 18991231
2/28/1900 19000227
2/29/1900 19000228
3/1/1900 19000301
1901 1901
1904 1904
1911 1911
1911 1911
1913 1913
1/5/2004 20040105
Winter 2004 2004
===========================

However, the worksheet function TEXT seems to perform the expected conversion.

Perhaps there is another fix?

=========================
Public Sub ConvertDates()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then
.Offset(0, 1).Value =
Application.WorksheetFunction.Text(.Value, "yyyymmdd")
Else
.Offset(0, 1).Value = Right(.Text, 4)
End If
End With
Next rCell
End Sub
========================



--ron