Pivot table Grouping (Issues with Dates and Formats)

[Gregor77]

Hey Guys

I have one that stumped me! I am using 2003 excel and I need to create a summary on aging stock. The data that I get is in CSV format imported as text into excel. It has the dates like this: yyyy-mm-dd. I tried to group the dates to be monthly. But excel PIV returns this message: Can not group selection. So I did some research and noticed blanks. So I created a dummy date to replace the blanks, eg: 1950-01-01. eg:=IF(M2=" ","1950-01-01",M2).

Now it still will not let me group the dates? So I checked the formats and it is all in TXT format (check). I tried to change the date to the numeric version "DATEVALUE(W2)" and the PIV will still not group it? I can't find a way to have it group the dates that I have on my data. Is there something that I am missing here?

Any help will be much appreciated!!!

 

[rallcorn]

try
=DATEVALUE(RIGHT(A1,2)&"/"&MID(A1,6,2)&"/"&LEFT(A1,4))

 

[rallcorn]

FYI - to get PT grouping to work, I had to add yet another column in data sheet (formatted as date) with formula = (cell w/ datevalue formula). Then I could use the final field in PT & group by months.

Tried copying the datevalue formula results & pasting as values & formatting as date, but that wouldn't work.

 

[Gregor77]

Wow, that worked! Thank you very much!!!!!

 

[rallcorn]

Glad to help!