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

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
210
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.

1633594976072.png


1633594951112.png


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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
210
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Oct 20, 2015
Messages
210
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,241
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 23, 2021
Messages
2,241
Office Version
  1. 365
Platform
  1. Windows
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) ?
 
Solution

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
210
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Board Regular
Joined
Oct 20, 2015
Messages
210
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:

VBA Code:
=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

Well-known Member
Joined
Feb 23, 2021
Messages
2,241
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,474
Messages
5,741,341
Members
423,656
Latest member
Medrok2021

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