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

[Guest]

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.

 

[Fredrik Wahlgren]

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.

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

 

[Fredrik Wahlgren]

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.

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]

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

| 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

 

[Fredrik Wahlgren]

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.

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

/Fredrik