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"

5

1 Answer

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.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy