I have a pivot table that pulls detection and respond times. the issue is i am trying to put it in a chart but the average times seem to be displaying wrong. the cell format is set to custom and is using dd:hh:mm but when the average is displayed in the chart the dd:hh:mm shows a 30 for the day instead of 00. how can i resolve this
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
pivot table formatting error
- Thread starter jjayden
- Start date
GraH
Well-known Member
- Joined
- Mar 22, 2020
- Messages
- 1,567
- Office Version
- 365
- Platform
- Windows
Custom formats playing a trick here. And it seems to be a pivot chart thing only.
In my sample tests 0 turned into 30 or 31, which seems to suggest it sees a date, and can't display "00", as that is the previous day of previous month. Hence it throws 30 or 31 at you.
Notice also the labels are off. In the pivot the format works, on the chart labels it does not (a day short?). I tried to display with 1904 date system, but then it turned totally weird.
A normal chart works however.
The solution would be to create a dynamic range on the pivot, so you can still create the chart, while using your correct format.
In my sample tests 0 turned into 30 or 31, which seems to suggest it sees a date, and can't display "00", as that is the previous day of previous month. Hence it throws 30 or 31 at you.
Notice also the labels are off. In the pivot the format works, on the chart labels it does not (a day short?). I tried to display with 1904 date system, but then it turned totally weird.
A normal chart works however.
The solution would be to create a dynamic range on the pivot, so you can still create the chart, while using your correct format.
Upvote
0
