Calculated fields in Pivot Tables (wince...)

  • Hi all,

    I wonder if anyone can help...I think this is a relatively straightforward query...but it has had me stumped for a couple of days now...

    I have a number of Pivot Tables in a spreadsheet report on the various activities of my consultants; activity reporting in other words. This activity is reported in 2hour chunks, with 4 chunks equalling 1 (normal) day i.e. 2hoursx4=8hours=1day.

    My pivot tables tend to report with a week number down the left column and a summary of project activity (under various categories) in the main data area. However this summary reports the number of 2 hour slots.

    So the question...

    How (using a calculated field or item) can I convert the sum of the 2 hour slots into days i.e. multiply them by 4?

    I am sure I will be appalled by how easy this is :duh:, but somehow I just cannot work it out by myself (despite numerous attempts).

    Thanks in advance.


    (note: I can post the spreadsheet as an attachment, if required)

  • I would hazard a guess it would be simpler to create a new field in your source data with the formula


    and then use this new field in your pivot table.

  • Hi there!

    Thanks for this!

    The data that the Pivot Table is counting are text fields e.g. it counts up the total number of times 'Assessment' comes up per week, and presents that. Unfortunately I cannot divide the text field by anything... :(

    I am sure there is a way to perform a calculation on one of the fields within the Pivot Table, but I am struggling to find the syntax to write the formula...

  • OK but surely if "Assessment" counts as 2 hours then you can have a field with a formula in your dataset


    if Assessment appears in Column C of your data range
    then add a new column called Days

    Then add Days to your PT - Summing.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!