I am trying to post a Sum of HOURS field in a pivot table & the HOURS field has values like HH:MM (60 minutes format)
But When i try to sum this in a Pivot table, The result value is shown as HH:MM (100 minutes format)
For example, sum of entries like 2.30 and 2.40 is giving me 4.80 but i expected it to be 5.10
Can you help me with correct format ?.
I tried to use interval function like =interval(Sum(HOURS)/24,'h.mm')) , but it's diving total by 24 instead of actual function.
There area three problems. Your expression is summing the hours before they are converted to a time. SO it will treat the .3 as a decimal and not a half hour. The sum needs to sit around the interval#
=Interval(Sum(Interval#(hours, 'h.mm')), 'h.mm')
Secondly, the format is wrong because you have set the format manually in the "Number" tab. The expression is forced to be Fixed at 2 decimal places, so it converts the number from a time format. Change it back to expression default.
Finally, your 10.3 will be read as 10 hours and 3 minutes, not 30 which I suspect it should be. You need to get your data collection to be a bit better, or affect the format of the data so that it properly reflects the time in a correct format.
So AB right now which has 5 sets of 10.3 hours reads as 50.15 hours and not 52.5. It may be that if there is only one number after the decimal place then you automatically add a 0 to it, but better you get the source data to be a proper time format.
@sunny_talwar That formula is again doing some division operation instead of formatting.
For example, It's converting 395.83 which is total Sum to 16.49
395.83/24 = 16.49
Yes. It's dividing Sum by 24. Not sure why. May be it has something to do with Pivot table funcationality.
=Sum(HOURS) it shows me 395.83
But your formula is giving me 16.49
I don't know why you see 16.49... did you remove the division by 24? My expression didn't divide by 24... but you original expression did...
=Interval(Sum(Interval#(HOURS, 'h.mm')), 'h.mm'))
I am not doing any division by 24. I am using the same formula as you proposed but it's internally dividing by 24 and producing wrong result.
It's kind of a bug it seems.
Are the numbers you are using decimals and not a time format, so .5 will not be interpreted as 30 minutes.
Turn it into a round number with minutes as the denominator.
Otherwise can you post the background data please as a table.