Pivot Table Not Displaying Negative Time Values

[fadetograham]

Hi everyone

I have a pivot that needs to show a planned journey time, actual journey time and then the variance. The problem I have is that the calculated field in the pivot won't display the negative values in the variance where the journey took less time than planned. It just displays #######, which invalidates the subtotals too.

Outside of the pivot table I can get the figures I need by using the below formula where E4 is actual time and D4 is planned time. When I adjust it for use in a calculated field (swapping e4 for 'actual' etc) it doesn't work. =IF(e4-d4<0, "-"&text(abs(e4-d4),"h:mm"), e4-d4)

Is there an adjustment to the basic =actual-planned formula in the calculated field which will get it to show the correct figure?

Thanks in advance for any help you can give.
Graham

 

[fadetograham]

I changed the cell format for the both columns in the data, updated the pivot and it is now showing all variances but they are showing as all positive.

How do get the negative values to show as such and the subtotals to account for them?

 

[RoryA]

You can probably format the displayed values using Conditional Formatting - as long as you have the two individual fields in the pivot too. The subtotals should be OK as they don't add up the values in between, they use exactly the same calculation.

 

[fadetograham]

Brill, it's not exactly how I envisaged it would be at the start but have got what I need in terms of numbers displaying and totals adding up in the pivot so I'm happy.

Thanks again for all your help.