Pivot table : Calc field to derive difference in dates - unexpected results

[scoobster]

Hi, Hope you can help me with my first post.

I have 3 columns which contain user name, start date + times, end date + time. What I want is the lowest start date and highest end date, and then to calculate the difference and show that in hours and minutes. I have a lot of data so I am using a pivot table.

The data I have for one user for one day is below;

<TABLE style="WIDTH: 214pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=286><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" span=2 width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #4f81bd; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl64 height=20 width=64>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #4f81bd; WIDTH: 83pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl64 width=111>Start Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #4f81bd; WIDTH: 83pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl64 width=111>End date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:03</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:03</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:03</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:03</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:03</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:04</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:04</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:05</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:05</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:06</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:06</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:16</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:16</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:17</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:17</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:17</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:19</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:19</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 02:20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 02:41</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 17:24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>19/03/2012 17:24</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 17:24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>19/03/2012 17:25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>user 1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>19/03/2012 17:25</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>19/03/2012 17:25</TD></TR></TBODY></TABLE>

Using the min function on Start Date I get : <TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=111><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20 width=111 align=right>19/03/2012 02:03</TD></TR></TBODY></TABLE>
Using the max function on End Date I get :
<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=111><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20 width=111 align=right>19/03/2012 17:25</TD></TR></TBODY></TABLE>

Both are correct and what I am expecting.

I am expecting the difference value to be 15:22 and when I use the following function in Excel,that is what I get;
End Date - Start Date

I have put the raw data into a pivot and created a calculated field to derive the duration between start date and end date for each user.

However, when I use the same formula as a calculated field in the pivot then I get 0:39 (formatted as [h]:mm or 0.027083333 (formatted as a number).

I found a document on the 'net that says to multiple the value by 24 so now the formula reads as follows
=(MAX('End date')-MIN('Start Date'))*24

The issue is that the result in the pivot is now 15:36 and this is slightly out from the expected 15:22.

I've tried all the different number and date / time formatting options. Is it a feature of calculated fields in a pivot or something else I'm doing wrong?

I'm using Excel 2010.

Thanks for any tips on how I can go about showing the expected value of 15.22.

 

[Laurent C]

Calculated fields in pivot tables actually work on aggregated values. The pivot will aggregate the values first, and then resolve the expressions.

In your calculated field, Excel sums the date columns then apply the formula you provided.

You can see this, if you enter a calculated field defined by
=MAX('End Date')
and then look at the returned value in the formula bar, you should see something like '27/02/4032 16:00:00'

I am not aware of any way to tell Excel how it should aggregate values in calculated fields.

This is something you could do with PowerPivot though, with some drawbacks (the update of the underlying data requires an additional step, drillthrough is not supported as of the current v1 release).

 

[scoobster]

Hello Laurent,

Sorry for the delay in replying.

I fixed the issue by adding the difference in end / start time to each row in the Excel range and then just used that column in the pivot.

Thanks
paul