Forum Discussion
Pivot Tables
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.
- 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