question about converting date format and pivoting

[ces005]

Hi,

If I have a dates in a table like 02/01/06, 02/10/06, etc. but want the date to be converted to 02/06 and 02/06 and then pivot on it this way, how do I do this? When I converted the format and then pivoted, these two entries appeared as two separate entries in the pivot table since the pivot table still treated these dates as being different but I want them to be treated as the same value.

-Eileen

 

[ces005]

Here is the logic for calculating the end column:

EndedOnCol = 1
' find column titled "Ended on"
Do Until (Cells(1, EndedOnCol).Value = "Ended_on")
EndedOnCol = EndedOnCol + 1
Loop

 

[ces005]

Here is my logic for calculating the ended on col:

EndedOnCol = 1
' find column titled "Ended on"
Do Until (Cells(1, EndedOnCol).Value = "Ended_on")
EndedOnCol = EndedOnCol + 1
Loop

 

[Datsmart]

Try replacing this code:

With Range(Cells(2, LastCol), Cells(LR, LastCol)) 
   .FormulaR1C1 = "=TEXT(EndedOnCol,""mm-yyy"")" 
   .Value = .Value 
End With

with this:

With Range(Cells(2, LastCol), Cells(LR, LastCol))
   .FormulaR1C1 = "=TEXT(" & EndedOnCol & ",""mm-yyy"")"
   .Value = .Value
End With

Putting your "EndedOnCol" variable inside a text string causes it to not see its' assigned value.

 

[Norie]

Eileen

Did you try my suggestion regarding grouping?

Are using that code to change the date format using TEXT?

If you are then you'll end up with text values that may not sort properly.

 

[ces005]

For the below line, I got the error
"Run-time error '1004': application-defined or object-defined error"

With Range(Cells(2, LastCol), Cells(LR, LastCol))

 

[ces005]

nevermind

Let me check to see what is going on.

I think the data source has some issue.

 

[Datsmart]

The following code change will allow you to keep your Date columns as true dates. The formula that is built sets each date to the first of the month so they will work as you want in your pivot table.

DateCol = Chr(EndedOnCol + 64) 'Assigns Letter value to EndedOnCol value
   With Range(Cells(2, LastCol), Cells(LR, LastCol))
      .Formula = "=DATE(YEAR(" & DateCol & "2),MONTH(" & DateCol & "2),1)"
      .Value = .Value
   End With

This code assumes the following:
EndedOnCol = Column # of "Ended_On" Text.
LastCol = Column # where you want the new formulae.
LR = Row # of bottom of new formulae range.

Hope this helps.

 

[ces005]

question about DateCol in equation

Hi,

Is DateCol the same as EndedOnCol in the above? Also, is there some way to do this without having a dependency on the analysis toolpak?

-Eileen

 

[ces005]

Nevermind -- I see how DateCol is calculated. Thanks.

 

[Datsmart]

The DateCol variable is the column "Letter", EndedOnCol is the column "Number". The formula in the code requires the letter of the column.

The code in this post has no need for Analysis Tookpak.
Do you have other code that needs it?