Date Format Glitch

[Brash Flagsnatcher]

I am trying to make a Pivot Table, but I keep getting an Unable to
Group This Selection error, which appears to be caused by a date
format glitch. Some of the dates in my report seem to format
perfectly, but there are a ton of them that appear to merely be read
as text, since when I sort by date, they don't sort correctly. Below
is an example - maybe someone can help me out? It is driving me crazy
and I see nothing bad in the data.

I can't attach a copy of the .xls file, since this is not
a binary group, but here are the .csv copy/paste entries.

This one works fine:

(Sales area, date, account name, amount)

Chicago,01-01-2007,Xerox,280


This one fails:

(Sales area, date, account name, amount)

Chicago,12/31/2008,Xerox,1188


The 12/31/2008 date won't re-format, no matter what
I do. The 01-01-2007 date formats to all the available
options in Excel. I also tried using Notepad to change
all the "/" marks into "-" marks, but without any luck.


BF

 

[Harlan Grove]

This one works fine:

(Sales area, date, account name, amount)

Chicago,01-01-2007,Xerox,280

This one fails:

(Sales area, date, account name, amount)

Chicago,12/31/2008,Xerox,1188

The 12/31/2008 date won't re-format, no matter what
I do. The 01-01-2007 date formats to all the available
options in Excel. I also tried using Notepad to change
all the "/" marks into "-" marks, but without any luck.

What are your Windows regional settings?

01-01-2007 is 1 Jan or Jan 1, but they're the same thing. OTOH,
12/31/2008 and 12-31-2008 would both be fubar as dates if Excel is
treating the 12 as day of month and the 31 as month of year.

Add a dummy record like

DeleteMe,31/12/2008,XYZ,999999999999

to your CSV file. How does Excel handle it upon import?

 

[Brash Flagsnatcher]

What are your Windows regional settings?

01-01-2007 is 1 Jan or Jan 1, but they're the same thing. OTOH,
12/31/2008 and 12-31-2008 would both be fubar as dates if Excel is
treating the 12 as day of month and the 31 as month of year.

Add a dummy record like

DeleteMe,31/12/2008,XYZ,999999999999

to your CSV file. How does Excel handle it upon import?

31-12-2008, cell formatted as mm-dd-yyyy

Regional option for short date is "10-Jan-2009"

 

[Harlan Grove]

Harlan Grove writes: ....

31-12-2008, cell formatted as mm-dd-yyyy

Regional option for short date is "10-Jan-2009"

OK, your problem is that Excel is using your regional settings in
which day of month comes before month of year, so 12/31 isn't valid,
so Excel treats it as text.

Simplest way to deal with this is to change the filename's extension
from .CSV to .TXT, and open the .TXT file. Excel will launch its Text
to Columns wizard upon opening the .TXT file (one of Microsoft's most
pigheaded nonactions is refusing to add an option to launch this
wizard when opening .CSV files). In the 3rd part of the wizard you can
choose DMY, MDY or YMD date format for specific fields.

 

[Brash Flagsnatcher]

OK, your problem is that Excel is using your regional settings in
which day of month comes before month of year, so 12/31 isn't valid,
so Excel treats it as text.

Simplest way to deal with this is to change the filename's extension
from .CSV to .TXT, and open the .TXT file. Excel will launch its Text
to Columns wizard upon opening the .TXT file (one of Microsoft's most
pigheaded nonactions is refusing to add an option to launch this
wizard when opening .CSV files). In the 3rd part of the wizard you can
choose DMY, MDY or YMD date format for specific fields.

Very strange that such a solution would succeed, but it does!

In closing, this has not happened to me in previous years when
I ran off the same report from the same database. Any idea what
must have changed in either my system or the database itself
to cause such a glitch?

Thanks for the help.

 

[Brash Flagsnatcher]

Very strange that such a solution would succeed, but it does!

In closing, this has not happened to me in previous years when
I ran off the same report from the same database. Any idea what
must have changed in either my system or the database itself
to cause such a glitch?

Thanks for the help.

I think I may have answered my own question. I changed the regional
date settings to MM/dd/yyyy and the separator to "/", and everything
worked like magic with the original .csv file. How it got switched
around is beyond me. Thanks again for putting me on the right track.