DD/MM/YY to MM/DD/YY
Posted by Chris on December 12, 2001 10:18 PM
I have written a short VBA routine to pull table data off of the Web and place the data in an Excel worksheet. Among other data types, the table has dates which are formatted as DD/MM/YY. However, Excel is attempting to auto format some as MM/DD/YY. Dates that are logical values of both formats (when the day AND month are 12 or less) cause the problems.
Example 1: 08/09/2001 becomes Aug 9 in the Excel worksheet and not Sep 9 and is formatted as a date which means the value of the cell becomes a numeric Excel date equivalent (9/8/01 = 37142)
Example 2: 25/08/2001 is fine and is formatted as general.
Any thoughts on maintaining the desired DD/MM/YY format without Excel taking over and mis-formatting some as MM/DD/YY?
Thank you.
Posted by Tom D. on December 13, 2001 9:46 AM
Assuming that the VBA program has the month and day correct, use
Selection.NumberFormat = "dd/mm/yyyy"
on the cell. If there is a problem with the month and day, then you may need to use the "month" and "day" functions in your program.
