How can I get my pivot table to display time formatted values correctly?

[gravanoc]

I created a pivot table a few days ago and cannot recreate the conditions in a new pivot table that should be similar. It is based off distance & time results from Google's Distance Matrix API, using the To Address for columns & For Address for rows, the To and From place names as Filters, and the Distance & Time as the Values. The Rows field also contains a Values item. I have both Distance & Time set to display the Sum, though Distance's number format is General and Time's number format is Custom (h:mm). The new table refuses to allow Time to be set as Sum.

I'm not experienced with pivot tables, so I know I did a lot of random experimentation until I arrived at the correct look for the original. Maybe someone can help me recall how I did it.

Some peculiarities about the original include: I set the appearance as tabular, no subtotals or grand totals are allowed, the OLAP Tools command is greyed out, and if I refresh the data it will inexplicably replace the valid time values with all 0:00's. That is why I am trying to recreate the original, to see if the time values are correct, and if so, to use VBA to constantly recreate the original so that times won't funk out. On the new pivot OLAP Tools isn't greyed out, and there are several additional pivot table options that are available depending on the tab.

Also posted here: How can I get my pivot table to display values with time format correctly?
I'll still mark as answer here even if already answered elsewhere, thanks.

 

[gravanoc]

Update, I posted here as well:

https://www.reddit.com/r/excel/comments/q37jf3

 

[gravanoc]

I've triple posted this problem and haven't a single reply to my credit. I can only conclude that I mistakenly entered the Konami code on my pivot table, allowing it to conform to any format ever devised despite the coding restrictions imposed by *cough* mortals. It's ascent to godhood is of little comfort to me while I'm bashing my skull against my keyboard like a rabid woodpecker, now bereft of all but its most primitive survival instincts.

 

[gravanoc]

Ok I believe I figured out what I've been doing wrong, as when I tried changing the number formatting I thought just selecting the Time field from the Values section would allow me to do that, but apparently I need to also have one of the pivot cells selected of that type, too. Or maybe it finally allowed me to change the format without good reason, just taking a guess.

The problem now is that both my new table and the original share the same defect, which is that the times are all 0:00. This was not originally a problem on the first pivot. I'm not sure why it's happening now, except for the possibility that I changed a relevant formula in the source table. I guess I'll need to post a new topic on this.

 

[Alex Blakenburg]

Can you share your workbook via Dropbox, google drive or any sharing platform.
We could try an xl2bb of some of your actual data sheet, if you want to try that first.

 

[Alex Blakenburg]

If you right click on any cell containing a time value in the pivot table you should get a Number Format option in the context menu.
Use this and to set a Custom formatting as [h]:mm
(your h:mm will not let you go past 24 hours)

I you are still getting 0:00 then the most likely issue is that it is seeing the underlying data as Text not time or a number.
If you change the number format on a time value in the data sheet does the format change ? if yes it is a number which is what you want if no it is being treated as text and needs to be converted.

There are various ways to convert it to a number and it will depend mainly on the workflow on how to attack it.
Is your distance and time in the same column in the original data or are they separate columns ?
How does the data get into the Data sheet (and how is it refreshed) ?

 

[gravanoc]

If you right click on any cell containing a time value in the pivot table you should get a Number Format option in the context menu.
Use this and to set a Custom formatting as [h]:mm
(your h:mm will not let you go past 24 hours)

I you are still getting 0:00 then the most likely issue is that it is seeing the underlying data as Text not time or a number.
If you change the number format on a time value in the data sheet does the format change ? if yes it is a number which is what you want if no it is being treated as text and needs to be converted.

There are various ways to convert it to a number and it will depend mainly on the workflow on how to attack it.
Is your distance and time in the same column in the original data or are they separate columns ?
How does the data get into the Data sheet (and how is it refreshed) ?

Hi Alex, thanks for the offer to help. I did manage to get it to utilize sum instead of count, but it will still display the time fields incorrectly as 0:00. In its current state the times are fine, but the problem occurs when I refresh the pivot cache. I think I updated the formula used to calculate the time fields on the source table, and that may be the issue. I did try the [h]:mm, but no luck so far.

Here is the file: Core Database - V69c.xlsm

 

[gravanoc]

Actually, Alex, it turns out that was the issue. So I realized after looking over the VBA responsible for placing the source table's formula that it would have translated as a string since I was using concatenation to add in the colon and such, so instead of a time value, so I added TIMEVALUE to the formula. The formula, btw, is:

=TIMEVALUE(INT(FILTERXML(RC[10],""//duration[1]/value"")/3600) &"":""&IF(" _
        & "INT(MOD(FILTERXML(RC[10],""//duration[1]/value""),3600)/60)<10,0&INT(MOD(FILTERXML(RC[10],""//duration[1]/value""),3600)/60),INT(MOD(FILTERXML(RC[10],""//duration[1]/value""),3600)/60)))

Edit: Well, I ended up changing it from TIMEVALUE to just VALUE since it didn't account for anything beyond the 24-hr duration. So it works now, but I'm still confused why TIMEVALUE wouldn't work in that situation.

Thanks again for your help.

 

[Alex Blakenburg]

I am not sure why TimeValue isn't working. It didn't work for me initially either and then magically decided it would work.
However value is going to be better for you anyway since you do have hours > 24 and TimeValue is dropping everything over 24 hrs.