
Originally Posted by
jhren
anr,
Here's the thing about the experiment I asked you to do...
When I have my system Regional Options set to display short date in d/M/yyyy format, I get the same result as you...
B1 = 29/12/2009 and B2 = 12/12/2009
However, if I have my system set for M/d/yyyy, I get...
B1 = #VALUE! and B2 = 11/1/2009
In both cases, the cell date format is "14/3/2001" and English (Australian) locale.
What this means, at least from a GUI perspective, is that a file with dates entered under Australian regional system settings is opened by a user with U.S. regional system settings, Excel interprets the values as entered. As such, 29/11/2009 is interpretted as text (thus the #VALUE! error in B1), while 12/11/2009 is interpretted as a date of 11/12/2009, which is correct for a U.S. user, but not when cell format is dd/mm/yyy... and then it is further processed by calculation, e.g. adding 1 month in B2, as 11/1/2009.
As I noted earlier, it is an application bug.
IMO, though I have not really attempted it, is to VBA process the textual dates and replace them with their date serial numbers (the cell format shouldn't have to change). This should result in correct dates no matter who (or should I say where?) opens the file, though the user may have to change cell format.
PS: Here's another interesting tidbit on the bug. While with d/M/yyyy system setting, I change the format of A1:B2 to General, all but A1 are converted to date serial number. A1 remains 29/11/2009.
Bookmarks