Summing of Duration (time) data

[Todgley]

I have a model in powerpivot that has a column that is storing duration data in the format "dd/mm/yy hh:mm:ss)

When I try to create a measure to sum the duration, the answer is different to a simple sum in the source spreadsheet.

I have applied the same cell formatting ([h]:mm) but the pivot table seems to have different values depending in if the duration is longer than 24 hours.

Any ideas on what is going wrong and how I can sum duration data.

Example showing problem: http://1drv.ms/1tJUA9X

 

[GDRIII]

I have a model where I show total time in 24:00:00 format by dividing my total time by 24 and then applying the 24:00:00 formatting in the Pivot.

I don't get days though, the values show up as 01/01/1900 dates but, it seems you could calculate number of days easily enough from there and then CONCATENATE them together.

 

[Todgley]

Thanks for the reply - I was hoping there would be a native way of doing this - it feels like a common scenario!

 

[scottsen]

You have dd/mm/yy, but that is not a date, it is a duration?

 

[Todgley]

Hi Scott and thanks for the reply.

The data is an export from another system which don't have control over. From what I can work out, the duration is all relative a zero date of 00/01/1900 00:00. So 00/01/1900 08:00 is 8 hour duration and 01/01/1900 08:00 is 32 hours duration.

If the cell is formatted as [h]:mm – it is presented fine in Excel.

The problem I am having is that in PowerPivot 00/01/1900 00:00 seems to change to a 30/12/1899 reference date. I am not sure why this is but it seems to screw around with some of the durations, effectively adding 24 hours to some of the entries.

 

[scottsen]

I have to admit, it certainly LOOKS like there is a bug in power pivot here when I was just playing with this a bit... lemme get back to you

 

[Todgley]

I have to admit, it certainly LOOKS like there is a bug in power pivot here when I was just playing with this a bit... lemme get back to you

Ah - that is interesting to hear!

I have found a work around in that if I create a calculated column that basically subtracts 1 from any duration value (in decimal format) >1. This means corrects the problem I was having where an extra 24 hours was being added to the longer duration entries (>24 hours)

I want to put my data in powerview so converting to hours might make more sense going forwards.