locked
Time Duration calcuation RRS feed

  • Question

  • Hi I've imported a set of data into my data model which includes a column of data that is a string data type representing a duration of time in the following format hours:minutes:seconds:milliseconds. What date time functions can I use to calculate (sum average etc.) these values . My thought is I need to change convert the data type from string to something else but not sure where to go from here. Thanks
    36:14:00
    58:35:00
    1:36:46
    40:25:00
    Wednesday, January 28, 2015 5:44 PM

Answers

  • Hi,

    when I import your sample data to Power Pivot (as linked table) it gets automatically converted to a Date
    it basically takes the lowest possible date-value (1899-12-31 00:00:00) and adds the imported value:

    Time
    01.01.1900 12:14:00
    02.01.1900 10:35:00
    30.12.1899 01:36:46
    01.01.1900 16:25:00

    30.12.1899 00:00:00

    so far so good. Next step is to create a calculated column as =CURRENCY([Time]) which converts your date/time to a numeric value which you can aggregate. lets call that column [Time_Numeric]
    (for some reason there is no DAX-function to convert it to float or double or any data type with more digits?!?)

    anyway, now you can do your SUM on top of it

    Total Duration:=SUM([Time_Numeric])

    in order to display the result correctly you may use this instead:

    Total Duration Date:=DATE(1900, 1, 1) + [Total Duration]

    Total Duration Formatted := FORMAT([Total Duration Date], "dd hh:MM:ss")

    those links might also help:
    http://www.powerpivot-info.com/post/299-q-i-have-a-field-in-the-pivot-that-shows-duration-on-some-event-in-seconds-how-can-i-create-calculated-measure-using-dax-that-would-convert-numeric-result-seconds-to-string-in-the-format-hhmmss
    https://social.technet.microsoft.com/Forums/systemcenter/en-US/e6057c4f-0e11-4afc-b541-119ad4ea2ad9/how-to-show-a-column-containing-seconds-duration-in-integer-format-as-hhmmss?forum=sqlkjpowerpivotforexcel

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, January 29, 2015 3:21 PM
    Answerer
  • you may need to convert it to date first - simply set the datatype of the column to Date
    in my case Power Pivot did import it as date automatically but u can also change the datatype manually

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, January 30, 2015 7:56 AM
    Answerer

All replies

  • Hi,

    when I import your sample data to Power Pivot (as linked table) it gets automatically converted to a Date
    it basically takes the lowest possible date-value (1899-12-31 00:00:00) and adds the imported value:

    Time
    01.01.1900 12:14:00
    02.01.1900 10:35:00
    30.12.1899 01:36:46
    01.01.1900 16:25:00

    30.12.1899 00:00:00

    so far so good. Next step is to create a calculated column as =CURRENCY([Time]) which converts your date/time to a numeric value which you can aggregate. lets call that column [Time_Numeric]
    (for some reason there is no DAX-function to convert it to float or double or any data type with more digits?!?)

    anyway, now you can do your SUM on top of it

    Total Duration:=SUM([Time_Numeric])

    in order to display the result correctly you may use this instead:

    Total Duration Date:=DATE(1900, 1, 1) + [Total Duration]

    Total Duration Formatted := FORMAT([Total Duration Date], "dd hh:MM:ss")

    those links might also help:
    http://www.powerpivot-info.com/post/299-q-i-have-a-field-in-the-pivot-that-shows-duration-on-some-event-in-seconds-how-can-i-create-calculated-measure-using-dax-that-would-convert-numeric-result-seconds-to-string-in-the-format-hhmmss
    https://social.technet.microsoft.com/Forums/systemcenter/en-US/e6057c4f-0e11-4afc-b541-119ad4ea2ad9/how-to-show-a-column-containing-seconds-duration-in-integer-format-as-hhmmss?forum=sqlkjpowerpivotforexcel

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, January 29, 2015 3:21 PM
    Answerer
  • Thanks Gerhard: When the "Time" column is brought into the data model it is recognized as string. When I try to use the CURRENCY() function I get this error message Calculation error in column 'HRTBL'[]: Cannot convert value '28:56:00' of type Text to type Currency. Am I missing something? I will look at the other links you posted as well.

    cheers

    Mike

     
    Thursday, January 29, 2015 6:55 PM
  • you may need to convert it to date first - simply set the datatype of the column to Date
    in my case Power Pivot did import it as date automatically but u can also change the datatype manually

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, January 30, 2015 7:56 AM
    Answerer
  • I will try this sorry I've been busy. My apologies for not responding sooner.  
    Thursday, February 12, 2015 8:22 PM