2 digit year in dates return 19xx not 20xx

[moranbo]

Dates entered with just the last two digits are handled in Excel as follows: between 00 and 29 are treated as 2000 to 2029; between 30 and 99 are treated as 1930 to 1999. It is possible to make an adjustment in Windows under Regional settings in Control Panel so that manual entry of dates in a spreadsheet with two digits will properly convert to 2000.

My problem is that the adjustment in Regional settings noted above does not apply to the text to column process in a macro. The input text file contains only two digit years (and the file cannot be changed to four digit years which would be the easy fix). The text file contains only dates meant to reflect dates after 2005 but the text to column process treats two digit dates in that file that are between 30 and 99 as 19xx, not 20xx.

I could write a macro to change the 19xx dates to 20xx but wanted to know if there is another way to tell Excel to use 20xx.

[Dave Peterson]

I couldn't find anything that would change this behavior (in xl2003).

But an alternative to the macro would be a formula that parses the string the
way you want.



moranbo wrote:
>
> Dates entered with just the last two digits are handled in Excel as
> follows: between 00 and 29 are treated as 2000 to 2029; between 30 and
> 99 are treated as 1930 to 1999. It is possible to make an adjustment
> in Windows under Regional settings in Control Panel so that manual
> entry of dates in a spreadsheet with two digits will properly convert
> to 2000.
>
> My problem is that the adjustment in Regional settings noted above does
> not apply to the text to column process in a macro. The input text file
> contains only two digit years (and the file cannot be changed to four
> digit years which would be the easy fix). The text file contains only
> dates meant to reflect dates after 2005 but the text to column process
> treats two digit dates in that file that are between 30 and 99 as 19xx,
> not 20xx.
>
> I could write a macro to change the 19xx dates to 20xx but wanted to
> know if there is another way to tell Excel to use 20xx.
>
> --
> moranbo
> ------------------------------------------------------------------------
> moranbo's Profile: http://www.excelforum.com/member.php...o&userid=27036
> View this thread: http://www.excelforum.com/showthread...hreadid=445590

--

Dave Peterson