How do I Preserve the format for a date field as mm/dd/yyyy in Pivot Table

Question

I am in need of my pivot table to preserve the date filed as mm/dd/yyyy. I have selected the field and attempted to set the custom value, I have unchecked the Autofit Column and made sure the Preserve Cell Formatting was checked. In experimenting I discovered that if I chose an individual cell (vs. Range of cells) that I could preserve the single cell by choosing the "Format" from the "Cells" task on the menu bar. With a Pivot Table that will have hundreds of potential cells this is not a practical approach to go cell by cell to make the change. Additionally, I found that I could select upwards of 10 cells and follow the above steps with success but strangely a single cell within the range would not format and would revert back to m/dd/yyyy. I need the mm/dd/yyyy in the Pivot Table for an import of records into another system that is not forgiving of the date format. Since the field is displayed as an element of a row the format cannot be set using the field settings option.

Field Settings when entered under "Rows"

Answer 1

That's ugly. I see the same behaviour in that latest version of Excel 365, so it's not limited to Excel 2010. I'm not sure there is a solution to be achieved with Excel's settings.

The only thing I can advise is to automate a manual format after each refresh. In the sheet with the pivot table, right click the sheet tab, then click "View Code". Paste the following code into your code window and adjust the column letter to suit your data layout.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Columns("L:L").NumberFormat = "mm/dd/yy;@"
    Debug.Print "ran macro to set date format in pivot table"
End Sub

Remember to save the file as a macro-enabled workbook with the .xlsm extension.

Answer 2

convert the 'date' column into 'text' using TEXT function [TEXT(value,"mm ddd yyyy")]. Use the latter (text) column in pivot instead of the first. Pivot will give you the date in the required format. The format can be changed as required.