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" wrote in message
...
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

"Phillip" wrote in message
...
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

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" wrote in message
...
| 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

"Jim Rech" wrote in message
...
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