# Thread: Pivot Table Calculated Field (Excel 2007)

1. ## 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. Can you post a worksheet sample of your work?

something like this may work .....

3. 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. Thank you Steve. That works perfectly. There can be no question about fractions of an hour now.

5. 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
•