×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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!

RE: Understanding Excel Date Conversion Rules

(OP)
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,

Just traded in my OLD subtlety...
for a NUance!

RE: Understanding Excel Date Conversion Rules

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

RE: Understanding Excel Date Conversion Rules

(OP)
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. 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

Quote (Skip)

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
http://newtonexcelbach.wordpress.com/

RE: Understanding Excel Date Conversion Rules

(OP)
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,

Just traded in my OLD subtlety...
for a NUance!

RE: Understanding Excel Date Conversion Rules

(OP)
Yes, except that xwb's results were somehow not consistent in the above referenced thread.

Skip,

Just traded in my OLD subtlety...
for a NUance!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!