Pivot Tables

%3CLINGO-SUB%20id%3D%22lingo-sub-2686048%22%20slang%3D%22en-US%22%3EPivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686048%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20building%20a%20Pivot%20table%20from%20the%20data%20shown%20below.%20It's%20energy%20usage%20data%20at%2030min%20intervals.%20Column%20C%20is%20the%20intervals%3B%20half%20hours%20%26amp%3B%20hours.%20Column%20F%20is%20the%20kWs.%20When%20the%20Pivot%20table%20is%20created%20from%20the%20data%20the%20hours%20are%20duplicated%20with%20the%20dash%20in%20between%20(see%20image%20highlights).%20It%20ultimately%20clutters%20the%20Pivot%20graph%20(not%20shown).%3C%2FP%3E%3CP%3EWhat%20is%20it%20doing%3F%3C%2FP%3E%3CP%3EHow%20do%20I%20format%20the%20Pivot%20Table%20to%20show%20just%20whats%20in%20Column%20C...%2030%20%2F%20100%20%2F%20130%20etc.%20NOT%2030-30%20%2F%20100-100%20%2F%20130%20%2F%20130%20etc%3C%2FP%3E%3CP%3EBetter%20yet%20-%20can%20I%20format%20to%20display%20-%2030%20as%2000%3A30%20%2F%20100%20as%2001%3A00%20%2F%20130%20as%2001%3A30%20etc%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PivotTable_DateTimeKWs.PNG%22%20style%3D%22width%3A%20791px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305910i9697CB00628948DD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PivotTable_DateTimeKWs.PNG%22%20alt%3D%22PivotTable_DateTimeKWs.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2686048%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2686256%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137772%22%20target%3D%22_blank%22%3E%40Aspirations%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20you%20have%20grouped%20your%20data%20in%20column%20HOUR.%20Just%20revert%20the%20grouping.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2686273%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686273%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks%20for%20the%20response%3C%2FP%3E%3CP%3EJust%20tried%20'UnGrouping'.%20The%20Pivot%20Table%20has%20converted%20all%20the%20entries%20to%2012%3A00%3A00%3F%20see%20IMAGE%20below%3C%2FP%3E%3CP%3EI%20next%20tried%20formatting%20the%20data%20in%20Column%20C%20to%20'Time'%2C%20and%20xls%20converted%20it%20all%20to%20'0%3A00'%3F%20see%20IMAGE%202%20below%3C%2FP%3E%3CP%3E....%20I'm%20new%20to%20Pivot%20Tables%20but%20is%20there%20something%20amiss%20with%20the%20source%20data%20(Column%20C)%3F%20Why%20is%20excel%20reading%20it%20as%20'Zero'%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PivotTable_DateTimeKWs-eg2.PNG%22%20style%3D%22width%3A%20854px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305930i1433ADBA80C6A797%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PivotTable_DateTimeKWs-eg2.PNG%22%20alt%3D%22PivotTable_DateTimeKWs-eg2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PivotTable_DateTimeKWs-eg3.PNG%22%20style%3D%22width%3A%20706px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305931i33F9EB68CBD881BD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PivotTable_DateTimeKWs-eg3.PNG%22%20alt%3D%22PivotTable_DateTimeKWs-eg3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2686282%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686282%22%20slang%3D%22en-US%22%3ECould%20you%20provide%20the%20workbook%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2687835%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2687835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137772%22%20target%3D%22_blank%22%3E%40Aspirations%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20dates%20are%20integer%20numbers%20starting%20from%20Jan%2001%2C%201900%20and%20time%20is%20decimal%20part%20of%20the%20number.%20For%20example%2C%20230%20is%20Aug%2017%2C%201900%2012%3A00AM%20(no%20decimal%20part%20here).%20If%20you%20apply%20TIME%20to%20integer%20it%20takes%20only%20decimal%20part%20(which%20doesn't%20exist%20in%20your%20case)%20and%20always%20returns%2012%3A00%20AM.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20convert%20your%20hours%20to%20hours%20in%20TIME%20format%20%3DHr%2F24.%26nbsp%3B%20For%20example%2C%20applying%20time%20elapsing%20format%20%5Bhh%5D%3Amm%20to%20result%20of%20%3D30%2F24%20calculation%20shows%2030%3A00.%20Just%20time%20format%20hh%3Amm%20will%20show%2006%3A00.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGrouping%20-%20it%20looks%20like%20you%20grouping%20is%20performed%20with%20minimum%20interval%201%20perhaps%20due%20to%20your%20time%20transformations.%20If%20you%20keep%20HOUR%20as%20integer%20and%20apply%2C%20for%20example%2C%20interval%2010%2C%20grouping%20will%20be%20as%2030-39%3B%2040-49%3B%20etc.%20Interval%20always%20is%20the%20same.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2688002%22%20slang%3D%22en-US%22%3ERE%3A%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2688002%22%20slang%3D%22en-US%22%3EHi%20Guys%2C%20I%20have%20a%20workbook%20with%2012%20sheets%2C%20one%20for%20each%20month.%20The%20sheets%20are%20based%20on%20the%20UK%20tax%20year%20and%20month%2C%20so%20that%20the%20first%20starts%20(Sheet%201)%20runs%20from%20the%206%20April%20to%205%20May%20and%20so%20on.%20On%20a%20separate%20sheet%20I%20want%20to%20gather%20data%20from%20the%20same%20cell%20on%20each%20of%20the%2012%20sheets%20but%20only%20the%20most%20up%20to%20date%20sheet.%20So%20if%20it%20was%20the%2012%20March%202021%20it%20would%20show%20me%20data%20from%20sheet%2012.%20I%20assume%20that%20using%20the%20TODAY()%20function%20would%20provide%20the%20most%20accurate%20method%20but%20I%20am%20struggling%20with%20this%20one.%20I%20would%20add%20that%20the%20workbook%20would%20only%20be%20used%20for%20a%20year%20and%20another%20created%20for%20the%20next%20year%20and%20so.%20Can%20you%20help%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

PivotTable_DateTimeKWs.PNG

8 Replies

@Aspirations 

It looks like you have grouped your data in column HOUR. Just revert the grouping.

 

@Detlef Lewin

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'?

 

PivotTable_DateTimeKWs-eg2.PNGPivotTable_DateTimeKWs-eg3.PNG

Could you provide the workbook?

@Aspirations 

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.

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?

@David_James 

What your are doing is called Threadnapping.

Please delete your post and start a new thread.

 

@Detlef Lewin 

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.

PivotTable_DateTimeKWs-eg4.PNG

Thank You for the response. I used your formula and got
30... 30:00
100... 100:00
130... 130:00. I will have a play around with it.
I also found the following formula to convert a 'value' into 'time'
=TIMEVALUE(LEFT(TEXT(B2,"0000"),2)&":"&RIGHT(TEXT(B2,"0000"),2))
and I got
30... 12:30AM
100... 1:00AM
130... 1:30AM
No idea how it works but it did