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

 

[Datsmart]

Excel only sees dates as "Serial Dates", not the way you format them. Select a cell and look in the formula bar and you will see how Excel sees them. Format your pivot table the same as your data table, then they will look the same.
If they came into the pivot table separately, then maybe some are "Text" instead of true dates.

 

[ces005]

If Excel sees the date as 1/5/2006. How do I change it so that Excel sees it as 1/2006?

 

[Datsmart]

If Excel "Sees" it as 1/2006, then it is no longer a number but text. You will not be able to run formulae against it. If that is what you want, add an apostrophe at the beginng of the number. Excel will view that as text.

 

[ces005]

The formula bar shows 1/05/06. I formatted the cell so it shows up as Jan-06.

What do I need to do so that the pivot table sees it as Jan-06 and not 1/05/06.

When I click on the entry in the pivot table and look at the formula bar, I see 1/05/06.

 

[Datsmart]

Select the date ranges in the pivot table, right click, choose format cells.
Format as desired.

 

[ces005]

I am trying to get Excel to combine the all data associated with one month into one row. Formatting the cell to display everything as the date in format Mar-06 format did not help. I see multiple entries in the pivot table with the same date (e.g. 15 entries showing Jan-06)

 

[Datsmart]

Formatting a date to view as "Mar-06" does not change the fact that it might be March 3rd or 4th. Excel can still see the difference, hence the multiple entries in your pivot table.
If you can add another column to your data table with the following formula you can use it in the pivot table instead of the date column.

=TEXT((A2),"mmm-yy")

This assumes your dates are in column A.
Paste the formula in row 2. Copy the formula down to the end of your data.
Give the column a Header name like "Month". Reset your pivot table to include the new column.

 

[ces005]

Hi,

How do I correct this logic?

Cells(1, LastCol).Value = "ConvertedDate"
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Range(Cells(2, LastCol), Cells(LR, LastCol))
.FormulaR1C1 = "=TEXT(EndedOnCol,""mm-yyy"")"
.Value = .Value
End With

I think it does not like this EndedOnCol value. I have logic earlier which calculates which column in the table contains the ended on date. Thanks.

-Eileen

 

[Norie]

Eileen

Why not just use the pivot tables Group functionality.

To access that right click the data field and select Group and Outline...

As to the code you posted, it's hard to tell exactly what the problem is without seeing where you've set EndedOnCol.

My guess is Excel is actually treating it as a named range.