Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations GregLocock on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Understanding Excel Date Conversion Rules

Status
Not open for further replies.

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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Replies continue below

Recommended for you

I just changed my region settings to UK and got 12-Jan displayed, as I would expect, and the cell contents show 12/01/2016 13/1 results in 13-Jan

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529
 
Okay, my assumption was incorrect. I was trying to understand how xwb could say that sometimes the conversion was base on MDY settings and other times DMY.

I suppose it will have to be a conundrum.

Thanks.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
SkipVought,

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
 
Yes, SAP uses such dates as I recall. It's been several years since I retired. Yes, YMD is unambiguous.

But here we're discussing the conversion of TEXT, i.e. [highlight #8AE234]12[/highlight]-[highlight #FCE94F]1[/highlight]/[highlight #729FCF]2[/highlight] to a DATE where the conversion result is essentially DateSerial([highlight #729FCF]2[/highlight]+2000,[highlight #8AE234]12[/highlight],[highlight #FCE94F]1[/highlight]) and the display is 12/1/2002 in MDY.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Skip said:
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.

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
 
DMY, yes. I agree.

Yes, 12/1 entered with DMY regional setting would convert to a DateSerial of day 12, month 1 for current year.


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Not following here. The import and text2columns all allow you to set the cell format type before the data gets there. There's no problem with that.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529
 
Yes, except that xwb's results were somehow not consistent in the above referenced thread.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor