Home |
Search |
Today's Posts |
#1
|
|||
|
|||
date formatting
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 |
#2
|
|||
|
|||
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. In article , "BernieH" wrote: 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 |
#3
|
|||
|
|||
On Mon, 30 May 2005 06:11:18 -0600, JE McGimpsey wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorrect Date formatting | Excel Discussion (Misc queries) | |||
Despite formatting a column in Excel 2002 worksheet as Short Date. | Excel Discussion (Misc queries) | |||
Conditional Formatting (Date vs Number) | Excel Discussion (Misc queries) | |||
problem with formatting cell to date format | Excel Worksheet Functions | |||
Date Formatting | Excel Discussion (Misc queries) |