On an import to excel a two digit date still is not right after c.

On my companies host system we store dates with only two digit years and we
use pivot dates to interpret correctly. When I create an extract of these
dates and import into excel they are not interpreted as the correct century.
I have already changed the regional settings to accomodate this and when I
just key a date into excel cell such as 12/1/30 it comes back correctly as
12/1/2030. However, the same date in my import will come out as 12/1/1930. I
think it has something to do with the import but I am not sure what.

Thanks for any suggestions.
--
PCastle

"Phillip" <[email protected]> wrote in message
news:[email protected]...
> On my companies host system we store dates with only two digit years and
we
> use pivot dates to interpret correctly. When I create an extract of these
> dates and import into excel they are not interpreted as the correct
century.
> I have already changed the regional settings to accomodate this and when I
> just key a date into excel cell such as 12/1/30 it comes back correctly as
> 12/1/2030. However, the same date in my import will come out as 12/1/1930.
I
> think it has something to do with the import but I am not sure what.
>
> Thanks for any suggestions.
> --
> PCastle

Open the Control Panel|Regional Options and click on the Date tab. Your
settings are probably such that a two digit year is interpreted to fall
between 1930 and 2029.

/Fredrik

As this article explains in a "Note" near the bottom:

Note While you can change the way the system interprets two-digit dates
under the Control Panel in Regional and Language Settings, Excel only uses
that setting when you enter dates manually. If you import a text file or
automate date entries by using Microsoft Visual Basic for Applications
(VBA), the fixed 2029 rule is in effect.

http://support.microsoft.com/?id=230931

So your date will be always be interpreted as 1930.

--
Jim

"Phillip" <[email protected]> wrote in message
news:[email protected]...
| On my companies host system we store dates with only two digit years and
we
| use pivot dates to interpret correctly. When I create an extract of these
| dates and import into excel they are not interpreted as the correct
century.
| I have already changed the regional settings to accomodate this and when I
| just key a date into excel cell such as 12/1/30 it comes back correctly as
| 12/1/2030. However, the same date in my import will come out as 12/1/1930.
I
| think it has something to do with the import but I am not sure what.
|
| Thanks for any suggestions.
| --
| PCastle

"Phillip" <[email protected]> wrote in message
news:[email protected]...
> On my companies host system we store dates with only two digit years and
we
> use pivot dates to interpret correctly. When I create an extract of these
> dates and import into excel they are not interpreted as the correct
century.
> I have already changed the regional settings to accomodate this and when I
> just key a date into excel cell such as 12/1/30 it comes back correctly as
> 12/1/2030. However, the same date in my import will come out as 12/1/1930.
I
> think it has something to do with the import but I am not sure what.
>
> Thanks for any suggestions.
> --
> PCastle

If my previous reply doesn't help, try this formula

=DATE(IF(YEAR(E17) < 1999,YEAR(E17)+100,YEAR(E17)),MONTH(E17),DAY(E17))

Adjust the cell reference and do a copy followed by Paste Special|Values to
insert corrected dates. Make sure the cells are formatted as Date, otherwise
you will get 47818

/Fredrik

"Jim Rech" <[email protected]> wrote in message
news:%[email protected]...
> As this article explains in a "Note" near the bottom:
>
> Note While you can change the way the system interprets two-digit dates
> under the Control Panel in Regional and Language Settings, Excel only uses
> that setting when you enter dates manually. If you import a text file or
> automate date entries by using Microsoft Visual Basic for Applications
> (VBA), the fixed 2029 rule is in effect.
>
> http://support.microsoft.com/?id=230931
>
> So your date will be always be interpreted as 1930.
>
> --
> Jim
>

Interesting. I haven't seen this KB article before. Anyway, I have suggested
a different approach to this problem.

/Fredrik

I think, but I'm not fully sure if it;'s what you're after, you can copy the dates into word, then set one of the columns in excel to FORMAT>Text and the PASTE SPECIAL back in to excel from Word as TEXT.

I had a similar porblem by the sounds of it with dates in XML, I played around with various importing and exporting with word for a while and got it in the format I needed as text but I can't fully remeber how I done it.