Understanding Excel Date Conversion Rules
Understanding Excel Date Conversion Rules
(OP)
This thread is a spin off of thread Scientific Papers On Genes Contain Conversion Errors Caused By Excel, Says Report.
It is an attempt to gain understanding of date conversion rules. I'm sitting in my sun-room in North Texas drinking coffee, while others of you may be in other locales enjoying other beverages. But the issue that joins us is that we are Excel users dealing with Excel's converting data that is entered, too often, changing what we intend to something else. And we all need to understand exactly what is happening, especially with dates.
Here in the US of A, my regional settings are MDY. Across the pond and in other places they are DMY. This, I believe (my first assumption), is a DISPLAY ONLY feature. This assumption, I would like to test.
When I enter 12-1 or 12/1 in a GENERAL formatted cell,
1) Excel assumes that a date is intended,
2) parses the string as month 12, day 1, current year,
3) converts those values to a date serial value of 42705 and
4) displays 12/1/2016.
So my prediction is that if your regional settings are DMY, and you perform entering the same value (12-1 or 12/1), Excel will execute the conversion rule, parsing 12 as month and 1 as day and assuming the current year, but then DISPLAY it as 1/12/2016 in DMY format. (=YourDate+31 ought to result in 1/1/2017)
Furthermore, if you were to enter 13-1 or 13/1 or 13/1/2006, no conversion takes place.
Is my assumption correct? Please set me straight if not.
Skip,
Just traded in my OLD subtlety...
for a NUance!
It is an attempt to gain understanding of date conversion rules. I'm sitting in my sun-room in North Texas drinking coffee, while others of you may be in other locales enjoying other beverages. But the issue that joins us is that we are Excel users dealing with Excel's converting data that is entered, too often, changing what we intend to something else. And we all need to understand exactly what is happening, especially with dates.
Here in the US of A, my regional settings are MDY. Across the pond and in other places they are DMY. This, I believe (my first assumption), is a DISPLAY ONLY feature. This assumption, I would like to test.
When I enter 12-1 or 12/1 in a GENERAL formatted cell,
1) Excel assumes that a date is intended,
2) parses the string as month 12, day 1, current year,
3) converts those values to a date serial value of 42705 and
4) displays 12/1/2016.
So my prediction is that if your regional settings are DMY, and you perform entering the same value (12-1 or 12/1), Excel will execute the conversion rule, parsing 12 as month and 1 as day and assuming the current year, but then DISPLAY it as 1/12/2016 in DMY format. (=YourDate+31 ought to result in 1/1/2017)
Furthermore, if you were to enter 13-1 or 13/1 or 13/1/2006, no conversion takes place.
Is my assumption correct? Please set me straight if not.
Skip,
Just traded in my OLD subtlety...for a NUance!





RE: Understanding Excel Date Conversion Rules
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Understanding Excel Date Conversion Rules
I suppose it will have to be a conundrum.
Thanks.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Understanding Excel Date Conversion Rules
This is why I have a strong preference for ISO's YYYYMMDD format. Open/Libre Office has some support for this, at least with output formatting.
I do not use numerical dates from Spreadsheets. 08-Sep and Sep-08 are unambiguous. 08-09 and 09-08 are ambiguous, whether or not you add the year.
--
JHG
RE: Understanding Excel Date Conversion Rules
But here we're discussing the conversion of TEXT, i.e. 12-1/2 to a DATE where the conversion result is essentially DateSerial(2+2000,12,1) and the display is 12/1/2002 in MDY.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Understanding Excel Date Conversion Rules
If the date format is DMY then 12-1/2 evaluates as Date(2+2000,1,12) = 37268.
On my system (Excel 2016) it displays as 12/01/2002, but if you enter 12/1 (with general format) it displays as 12-Jan.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Understanding Excel Date Conversion Rules
Yes, 12/1 entered with DMY regional setting would convert to a DateSerial of day 12, month 1 for current year.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Understanding Excel Date Conversion Rules
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Understanding Excel Date Conversion Rules
Skip,
Just traded in my OLD subtlety...
for a NUance!