MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Strange Date 'result' from inputbox


Posted by g-man on April 26, 2001 7:10 PM

I am getting a really weird result from an inputbox that requests an associate's date of hire.

If I enter the date as '3/26/29' the result displayed in the referenced cell is 3/26/2029.

If I enter the date as '3/26/30' the result displayed in the referenced cell is 3/26/1930.

Does anyone have a thought on why this is occurring?

Thx
g-man


Posted by Kevin James on April 26, 2001 7:42 PM

g-man:

Its not related to your input box. It is related to how Excel handles dates.

Posted by Kevin James on April 26, 2001 8:20 PM

g-man:

I guess I could have given you a little more info.

In Excel Help, choose the Find tab and enter 1930.

Microsoft's logic is there for you to read.

Posted by g-man on April 26, 2001 9:16 PM

Kevin,

Thanks for the insight.

Microsoft logic. What an oxymoron.


g-man


Posted by Richie Turner on April 27, 2001 4:29 AM

In Dat and Time under Control Panel there's an option that tell windows how to recognises two-digit dates. By default it is set so that anything less than 30 is assumed to be 20xx and anything more than 30 is assumed to be 19xx. Uou can set it as you wish

hope it helps

Richie

Posted by Mark W. on April 28, 2001 5:13 PM

g-man, this behavior is documented in the Excel
Help topic for "How Microsoft Excel performs
date and time calculations". And, for the record
this was an approach used by many software vendors
(both PC and mainframe) as a quick fix for the Y2K
problem.

You can avoid this problem altogether by entering
your dates in m/d/yyyy format. After all it was
m/d/yy data entry and storage that got us in
trouble in the first place.

Posted by Mark W. on April 28, 2001 5:18 PM

And, By the Way...

Scientific American, has an excellent article on
the subject at http://www.sciam.com/1999/0199issue/0199dejager.html#link3 .
There's a SUBTOPICS sidebar labelled "Doing Windows"
that discusses this the implications of this "fix".