Just over a week ago, I detailed how I transfer strings that begin with a leading zero from my Delphi programs to Excel. Unfortunately the transfer mechanism causes problems with a different type of field: dates. Using the 'csv method', dates are passed as strings, so today's date should appear as 03/11/2020 (there's a leading zero here as well, but Excel knows that this isn't a number because of the slashes). The 'varArray method' has to format the columns prior to data being transferred: I use a constant xlDateFormat that is set to 'dd/mm/yyyy'. Surely this is correct as 03/11/2020 is dd/mm/yyyy.
But no: Excel seems to have a bug that causes the month and day to be swapped as long as the day is not greater than 12. So 12/11/2020 would appear as 11/12/2020 (American format) but the following day, 13/11/2020 would appear as 13/11/2020, which is apparently a month before the day that precedes it!
The initial solution was to use a different value for xlDateFormat: d/m/yyyy. This causes today's date to appear as 3/11/2020 which is much better than 11/03/2020 but still not totally correct. Writing this blog entry has caused me to wonder whether there is a better solution (drum rolls, please).
Counter-intuitively, the optimal solution is ... not to format date columns! 03/11/2020 appears as 03/11/2020. I live and learn.
No comments:
Post a Comment