3
I find a strange inconsistency when copying-pasting dates on MS Excel, when they have a 2 digits year. I work on a machine Windows 7 Enterprise 64 bit and my Excel is a 2010 version.
PROBLEM DESCRIPTION: if I write a short-date into any application (let's say the Notepad), and I copy this to Excel, the year will be wrongly interpreted after 2029. Examples:
- I write "12/01/29" on Notepad and copy-paste this value on MS Excel: the date will be interpreted as "12/01/2029".
- I write "12/01/31" on Notepad and copy-paste this value on MS Excel: the date will be interpreted as "12/01/1931" (instead of "12/01/1931").
RESEARCH 1 - control panel: After first research, I found an option on the Control Panel / Dates configuration, saying that "if a year is inserted with 2 digits, it will be included between 1997 and 2096". Picture below (it's in French, my system language, but the content is the one I described):
However, even if this setting would lead me to expect a proper conversion of the test-case above, it's not like this.
FURTHER RESEARCH - VBA: VBA is usually inheriting the settings of the hosting application (MS Excel in my case), which is inheriting the setting of the system. For example, if the system language is French, it means that the month of "December" is written as "Décembre". So, if I write this it will work: myDate = DateValue("11-déc-12"), while if I write this it won't work: myDate = DateValue("11-dec-12"). This is clearly because VBA inherits the system language, so it recognizes "décembre" (French) and not "december" (English).
However, if I write this:
myDate = DateValue("12/01/31")
the value will be properly interpreted as 12/01/2031.
FURTHER FINDING: typing manually "12/01/31" on an Excel cell will be properly converted into 2031. So it seems the problem arises on copy-paste only.
MY QUESTION: It seems there's an inconsistency between settings. Could anyone lead me to the proper place to fix this issue once forever, and make sure that the setting "if a year is inserted with 2 digits, it will be between XXXX and YYYY" please? Thank you in advance, don't hesitate to ask more details if needed.
Matteo NNZ
Posted 2014-12-17T10:48:16.467
Reputation: 131
Did you come across this help file? http://office.microsoft.com/en-gb/excel-help/change-the-date-system-format-or-two-digit-year-interpretation-HP010054141.aspx
– freekvd – 2014-12-17T11:05:11.793Yes @freekvd, that's actually where I found out about "Change the way two-digit years are interpreted", which is the solution I proposed at my "Research 1". It doesn't work tough :/ – Matteo NNZ – 2014-12-17T11:07:50.617
It seems to be a bug in Excel's DATEVALUE worksheet function. Regardless of the Windows date configuration,
=DATEVALUE("1/1/31")will always return 11324. Shown as a date, this results in 1-1-1931. – freekvd – 2014-12-17T12:14:58.657@freekvd this is strange, if I put a
MsgBox myDateI see the proper date. However, I had given the VBA tip just for showing the inconsistency, but I agree with you that the solution to this rebus is not lying there! – Matteo NNZ – 2014-12-17T13:13:53.547