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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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
76,303
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.
 

Forum statistics

Threads
1,141,429
Messages
5,706,402
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top