SkipVought
Aerospace
- Jun 20, 2006
- 743
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!