Aug 25 2021 04:17 PM
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
Aug 25 2021 07:33 PM
Aug 25 2021 08:16 PM
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'?
Aug 26 2021 06:07 AM
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.
Aug 26 2021 06:42 AM
Aug 26 2021 06:47 AM
What your are doing is called Threadnapping.
Please delete your post and start a new thread.
Aug 26 2021 03:14 PM
Future work on the spreadsheet revealed some strange occurrences so I started again.
This time I realised my persistent problem was the entries in Column B (HOUR) were not time values. Searching the Web I found the formula...... to convert the value to TIME (see column C)
......=TIMEVALUE(LEFT(TEXT(B2,"0000"),2)&":"&RIGHT(TEXT(B2,"0000"),2))
No idea how the formula works but copy and paste is a wonderful thing.
Thank You for you time. It defiantly helped.
Aug 26 2021 03:28 PM