Forum Discussion
Pivot Tables
I'm building a Pivot table from the data shown below. It's energy usage data at 30min intervals. Column C is the intervals; half hours & hours. Column F is the kWs. When the Pivot table is created from the data the hours are duplicated with the dash in between (see image highlights). It ultimately clutters the Pivot graph (not shown).
What is it doing?
How do I format the Pivot Table to show just whats in Column C... 30 / 100 / 130 etc. NOT 30-30 / 100-100 / 130 / 130 etc
Better yet - can I format to display - 30 as 00:30 / 100 as 01:00 / 130 as 01:30 etc?
Thank You
8 Replies
- Hi Guys, I have a workbook with 12 sheets, one for each month. The sheets are based on the UK tax year and month, so that the first starts (Sheet 1) runs from the 6 April to 5 May and so on. On a separate sheet I want to gather data from the same cell on each of the 12 sheets but only the most up to date sheet. So if it was the 12 March 2021 it would show me data from sheet 12. I assume that using the TODAY() function would provide the most accurate method but I am struggling with this one. I would add that the workbook would only be used for a year and another created for the next year and so. Can you help?
What your are doing is called Threadnapping.
Please delete your post and start a new thread.
Thanks for the response
Just tried 'UnGrouping'. The Pivot Table has converted all the entries to 12:00:00? see IMAGE below
I next tried formatting the data in Column C to 'Time', and xls converted it all to '0:00'? see IMAGE 2 below
.... I'm new to Pivot Tables but is there something amiss with the source data (Column C)? Why is excel reading it as 'Zero'?
In Excel dates are integer numbers starting from Jan 01, 1900 and time is decimal part of the number. For example, 230 is Aug 17, 1900 12:00AM (no decimal part here). If you apply TIME to integer it takes only decimal part (which doesn't exist in your case) and always returns 12:00 AM.
To convert your hours to hours in TIME format =Hr/24. For example, applying time elapsing format [hh]:mm to result of =30/24 calculation shows 30:00. Just time format hh:mm will show 06:00.
Grouping - it looks like you grouping is performed with minimum interval 1 perhaps due to your time transformations. If you keep HOUR as integer and apply, for example, interval 10, grouping will be as 30-39; 40-49; etc. Interval always is the same.
