I receive excel data from six outside agencies on a monthly schedule. One of
the columns is Date. When I put all the data together on one ss, I can create
a pivot table but I cannot group the dates. (I know it is a date format
problem as I created a dummy ss from scratch and was able to create the
chart.) Each date cell has a green tic in the top left corner. Format|Cells
tells me it's a date but the infobox next to each date cell shows me a list
of options. How can I take the existing column of dates and maybe move them
to a new column with a correct date format so that I can use that column in
my pivot table? Hope that's clear! Thanks...

Most likely they are seen as text, things to try, copy an empty cell, select
teh dates and do editpaste special and select add (you might have to
reformat them to display as dates), 2. select the column and do datatext to
columns, click finish
as soon as the wizard opens. If that does not work you might have hidden
trailing characters, then you can use a macro here

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards,

Peo Sjoblom

"Dan" wrote:

I receive excel data from six outside agencies on a monthly schedule. One of
the columns is Date. When I put all the data together on one ss, I can create
a pivot table but I cannot group the dates. (I know it is a date format
problem as I created a dummy ss from scratch and was able to create the
chart.) Each date cell has a green tic in the top left corner. Format|Cells
tells me it's a date but the infobox next to each date cell shows me a list
of options. How can I take the existing column of dates and maybe move them
to a new column with a correct date format so that I can use that column in
my pivot table? Hope that's clear! Thanks...

Peo:
Each cell has a date like this: '1/5/04, meaning it's probably text. If I
delete the leading ' I can make my pivot chart. However, I can't seem to
overide the cell formatting that I receive from each group. I'll try your
suggestions. Let you know!

"Peo Sjoblom" wrote:

Most likely they are seen as text, things to try, copy an empty cell, select
teh dates and do editpaste special and select add (you might have to
reformat them to display as dates), 2. select the column and do datatext to
columns, click finish
as soon as the wizard opens. If that does not work you might have hidden
trailing characters, then you can use a macro here

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards,

Peo Sjoblom

"Dan" wrote:

I receive excel data from six outside agencies on a monthly schedule. One of
the columns is Date. When I put all the data together on one ss, I can create
a pivot table but I cannot group the dates. (I know it is a date format
problem as I created a dummy ss from scratch and was able to create the
chart.) Each date cell has a green tic in the top left corner. Format|Cells
tells me it's a date but the infobox next to each date cell shows me a list
of options. How can I take the existing column of dates and maybe move them
to a new column with a correct date format so that I can use that column in
my pivot table? Hope that's clear! Thanks...

Peo:
Your first suggestion worked fine!!! Thanks...

"Peo Sjoblom" wrote:

Most likely they are seen as text, things to try, copy an empty cell, select
teh dates and do editpaste special and select add (you might have to
reformat them to display as dates), 2. select the column and do datatext to
columns, click finish
as soon as the wizard opens. If that does not work you might have hidden
trailing characters, then you can use a macro here

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards,

Peo Sjoblom

"Dan" wrote:

I receive excel data from six outside agencies on a monthly schedule. One of
the columns is Date. When I put all the data together on one ss, I can create
a pivot table but I cannot group the dates. (I know it is a date format
problem as I created a dummy ss from scratch and was able to create the
chart.) Each date cell has a green tic in the top left corner. Format|Cells
tells me it's a date but the infobox next to each date cell shows me a list
of options. How can I take the existing column of dates and maybe move them
to a new column with a correct date format so that I can use that column in
my pivot table? Hope that's clear! Thanks...