Problem with date formatting
Posted by Dave on September 25, 2000 6:42 AM
I am trying to record a macro, part of which changes a column of dates from standard English format (dd/mm/yy) to yyyymmdd, after having converted the year part from 0000 to 2000. It runs on a .csv file.
When I carry this out manually, it works fine, but when I record it in a macro, it does not properly change the format. Each cell is still dd/mm/yy, but if I double click the cell, then move to another, it appears correctly as yyyymmdd. The properties for the whole column say yyyymmdd
The macro goes on to save the file, and when re-opened, the format is "general" and still appears dd/mm/yy.
The whole procedure works fine while I'm recording it. But running the same macro throws up this problem.
The generated code is as follows:
Columns("B:B").Select
Selection.Replace What:="/0000", Replacement:="/2000", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Columns("B:B").Select
Selection.NumberFormat = "yyyymmdd"
Any ideas? Thanks.
Posted by David on September 26, 2000 1:59 AM
to put a date in a cell it needs to be formatted as a date without text in it. Ahh I can't really explain it but try something like this where you copy you value format the cell and put it back in
Sub formatdate()
numcol = 3
numRows = 3
For col = 1 To numcol
For Row = 1 To numRows
temp = Cells(Row, col)
Cells(Row, col).Clear
Cells(Row, col).NumberFormat = "yyyymmdd"
Cells(Row, col) = temp
Next Row
Next col
End Sub
Posted by David on September 26, 2000 2:00 AM
to put a date in a cell it needs to be formatted as a date without text in it. Ahh I can't really explain it but try something like this where you copy you value format the cell and put it back in
Sub formatdate()
numcol = 3
numRows = 3
For col = 1 To numcol
For Row = 1 To numRows
temp = Cells(Row, col)
Cells(Row, col).Clear
Cells(Row, col).NumberFormat = "yyyymmdd"
Cells(Row, col) = temp
Next Row
Next col
End Sub
Posted by Ivan Moala on September 26, 2000 2:56 AM
Dave
Try setting yoyr system date format
under the Control Panel = regional settings
Ivan
Posted by Dave on October 05, 2000 6:52 AM
Thanks very much - worked perfectly!
