Date format (ROW) in Pivot Table

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
Hi,

I've created a pivot table is based on a worksheet of data. For simplicity, let's assume the worksheet has two columns:

Date
Amount

The pivot table is setup with "Date" as the ROW and "Sum of Amount" as the COLUMN.

The problem is, that on the data worksheet, the format of the Date column is "Mar-98" - but, the pivot table displays the date as "3/1/1998". I want the pivot table to preserve the formatting (i.e., "Mar-98"). I know that for COLUMN data in the pivot table, you can set the NUMBER format. However, I don't see how to do this with ROW data.

Any suggestions?

Thanks and regards,

T.J.
 

Excel Facts

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

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
Select the date data in the pivot table and right click.
Select FORMAT CELLS.
Select NUMBER TAB.
Select Date and format as you like.
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
Thank you for the response.

However, this solution only works if no new rows will be added in the future. Any new row that gets added to the pivot table reverts back to the "3/1/1998" format.

Any other suggestions?

Thanks for the time.
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
Good idea. I will do that unless someone else has a better idea.

Thanks again for the help.
 

Forum statistics

Threads
1,147,696
Messages
5,742,679
Members
423,747
Latest member
Shadeslayers09

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