Search code examples
timesql-server-2008-r2pivot-tableexcel-2013

Pivot Table won't sum SQL Time type

I haven't been able to find an answer to this all over the interwebs. This is the issue I have:

I have an SQL View that has a Time type field, which includes the Time an employee stayed logged into a system.

When I create a Pivot Table in Excel to get the Total Time agent has spent logged in, Excel won't sum the Time field, it can count it, but not sum or average. Gives me 0 in both Sum and Average, but in Count it shows 24 (24 hour intervals).

I have tried multiple convert/casts to translate the Time data to a format that Excel understands (it seems to think it's a varchar/string type) but to no avail.

Any suggestions?

Solution

  • So this is what I ended up doing for the sake of someone who might run in the same problem as I have:

    First on my SQL query I transformed the Time data to an integer (in seconds) by using the following:

    (DATEPART(hh, Login_Time) * 60 * 60) + (DATEPART(MINUTE, Login_Time) * 60) + DATEPART(s, Login_Time) as Login_Time_Secs

    Then in my Pivot Table in Excel I created a Calculated Field that did the opposite calculation, by transforming the seconds into Excel Time:

    Login_Time = Login_Time_Secs/(60*60*24)

    Formatted the Login_Time field to an Hour format [h]:mm

    Done! Now my Pivot Table in Excel can Sum/Average the time logged in per employee.

    This didn't require for me to change my SQL data source (which would be a big hassle), it only changed the View Query to where the Pivot Table connects to.

    I hope this helps someone.