Pivot table Value Wrongly calculates Interval

[voxjungle]

Hi,

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.

[voxjungle]

@Anonymous, This is also not working for me. It gives me same result as with Sunny's formula

[MarkWillems]

OK, could you  post the example qvw to take a look at?

[voxjungle]

PFA my qvw file

[MarkWillems · Accepted solution]

Hey

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.

[voxjungle]

Thanks. that helped

[sunny_talwar]

So, I guess my initially provided expression was not incorrect after all 🙂