question about converting date format and pivoting

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Board Regular
Joined
Mar 13, 2006
Messages
189
If Excel sees the date as 1/5/2006. How do I change it so that Excel sees it as 1/2006?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Board Regular
Joined
Mar 13, 2006
Messages
189

ADVERTISEMENT

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Select the date ranges in the pivot table, right click, choose format cells.
Format as desired.
 

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189

ADVERTISEMENT

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
Code:
=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

Board Regular
Joined
Mar 13, 2006
Messages
189
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,038
Members
410,583
Latest member
gazz57
Top