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

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