Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inconsistent date conversion
Fell into an Excel trap with date conversions. Anyone met this one
before? The following code accumulates dates in a (potentially large) array which is then copied to a worksheet in a single assignment. Unfortunately, in the process Excel converts the dates to text but does so inconsistently. The second date should be 12th May, but cell contains 5th Dec. My workaround is to use format() to do my own conversion as in the fourth date. (I'm using Excel v2002 and the UK locale but don't think this makes any difference.) Bye for now Chris Jakeman Option Base 1 Sub DateProblem() Dim rData As Range Dim aData(4, 1) As Variant aData(1, 1) = DateSerial(2004, 1, 31) ' Shows as "1/31/2004" aData(2, 1) = DateSerial(2004, 5, 12) ' Shows as "2004/Dec/05" aData(3, 1) = DateSerial(2004, 5, 13) ' Shows as "5/13/2004" aData(4, 1) = Format(DateSerial(2003, 2, 28), "yyyy/mm/dd") With Worksheets("Date Conversion") Set rData = .Range(.Cells(1, 1), .Cells(4, 1)) End With rData.Value = aData ' Array copied to worksheet range End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inconsistent date conversion
Chris,
I too have been trapped in the past with this sort of date issue. The rule I've learned is to always write a cell using a Date variable. If you try to write a cell using a String variable, you'll end up relying on Excel's own date conversion, which leans toward US oriented date formatting. In the case of "12/05/03", it becomes 5-Dec-2003. If I have to use a String variable, I try to format the month as mmm. Here's an example which highlights the issues (US computers will see the same date, UK will see A1 being different from A2, A3) Sub test() Dim str1 As String, str2 As String, dtm As Date str1 = "12/05/03" '12-May-2003 str2 = "12-May-03" '12-May-2003 dtm = DateSerial(2003, 5, 12) '12-May-2003 Range("A1").Value = str1 Range("A2").Value = str2 Range("A3").Value = dtm End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "Chris Jakeman" wrote in message m... Fell into an Excel trap with date conversions. Anyone met this one before? The following code accumulates dates in a (potentially large) array which is then copied to a worksheet in a single assignment. Unfortunately, in the process Excel converts the dates to text but does so inconsistently. The second date should be 12th May, but cell contains 5th Dec. My workaround is to use format() to do my own conversion as in the fourth date. (I'm using Excel v2002 and the UK locale but don't think this makes any difference.) Bye for now Chris Jakeman Option Base 1 Sub DateProblem() Dim rData As Range Dim aData(4, 1) As Variant aData(1, 1) = DateSerial(2004, 1, 31) ' Shows as "1/31/2004" aData(2, 1) = DateSerial(2004, 5, 12) ' Shows as "2004/Dec/05" aData(3, 1) = DateSerial(2004, 5, 13) ' Shows as "5/13/2004" aData(4, 1) = Format(DateSerial(2003, 2, 28), "yyyy/mm/dd") With Worksheets("Date Conversion") Set rData = .Range(.Cells(1, 1), .Cells(4, 1)) End With rData.Value = aData ' Array copied to worksheet range End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inconsistent date conversion
"Rob van Gelder" wrote in message ...
Chris, I too have been trapped in the past with this sort of date issue. The rule I've learned is to always write a cell using a Date variable. If you try to write a cell using a String variable, you'll end up relying on Excel's own date conversion, which leans toward US oriented date formatting. In the case of "12/05/03", it becomes 5-Dec-2003. Here's an example which highlights the issues (US computers will see the same date, UK will see A1 being different from A2, A3) Reproduced as you predicted on my UK computer. I used to use the Date type, but now I'm using an array to fill many rows of a sheet in one assignment, I can't use it. This special assignment only work for arrays of type Variant. I plan to continue using an array in this application, because it's so much faster, but I'll just have to take precautions. Thanks for your help. Bye for now, Chris Jakeman Tiny IT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date inconsistent help please :) | Excel Worksheet Functions | |||
Date pasted web site causing inconsistent date format | Excel Worksheet Functions | |||
Date conversion | Excel Worksheet Functions | |||
Date formatting inconsistent | Excel Worksheet Functions | |||
Date Conversion | Excel Worksheet Functions |