Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Table Calculated Field (Excel 2007)

    I am creating a Pivot Table for which one of the fields of data contains "Duration", a calcultion that results in minutes (no fractions).

    I can insert a Calculated Field to sum the results in a Pivot Table and divide by 60 to show hours and fractions of an hour.

    But I would perfer to show Hours and Minutes like so:
    =CONCATENATE((B30-MOD(B30,60))/60," hr ",MOD(B30,60)," m")
    This formula works within the worksheet itself, and as one example, results in "2 hr 23 m". When I enter this into the Insert Calculated Field Formula box, I change the cell reference B30 to the table field Duration, and then get a #VALUE! error.

    Is there a way to dislpay hh:mm by correcting this formula? I tried a number of number formats for time, but get unexpected results (140 minutes displays as 8:00).

    Perhaps there's a better number format to use. I'd rather not show time in hours and tenths of hours.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Can you post a worksheet sample of your work?


    something like this may work .....
    Attached Images Attached Images
    Last edited by tfspry; 2011-04-06 at 23:34. Reason: added my sample

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can't just divide by 60 if you want to use EXCEL Date/time. Excel stores dates and times in units of DAYS. You must divide minutes by 60 and 24 (divide minutes by 1440). If you divide 140 by 1440 and format as time you will not get 8 but 2:23. When you divide by 140 by 60 you get 2.333 which excel interprets as 2 and one-third day of 2 days and 8 hrs. You ask to display the hours-min so it displays 8:00

    If you divide by minutes by 1440 and format-custom as:
    h" hr "mm" m"

    You can display the time directly without your formula...

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thank you Steve. That works perfectly. There can be no question about fractions of an hour now.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    One comment, If the hours can be >24 the format I posted will ignore the "day part". If that may occur you probably want elapsed time, not time of day and should use the format-custom:
    [h]" hr "mm" m"

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •