locked
PowerPivot and time field formatting RRS feed

  • Question

  • Hello! I have a simple data set which includes a time column. When I PowerPivot on this data set, the time format is not properly presented in the pivot table. 

    the data contains the total hour format e.g: 

    458:30:00
    1143:00:00
    163:09:00
    1755:33:00
    1751:14:00
    5263:55:00

    and I searched through the pivot table data format to use: [h]:mm:ss;@ as a format, but I didn't found in addition to when I used these data into the pivot, I got wrong findings as I saw this used data take a Text format


    Thursday, February 16, 2017 7:56 AM

Answers

  • PowerPivot only has a fixed set of formats and [h]:mm:ss;@ is not one of them.

    However if you convert this column to a decimal you will see that it becomes a number where the integer portion is the total number of days and the decimal portion represents partial days. So 36 hours is 1 day and 12 hours and becomes a value of 1.5. You can then convert this back to a string representation using a formula like the following:

    FormattedTime:=format(int(Sum([Time]),"0") & ":" & FORMAT( Sum([Time] - int(Sum([Time]))*60, "00")

    This is a string measure so it will be left aligned by default, but this is the only way I can think of to get the formatting you are after.


    http://darren.gosbell.com - please mark correct answers

    Friday, February 17, 2017 3:08 AM

All replies

  • Hi Ahmed M Abdel Kader,

    I locate your sample data in Excel. I use Excel 2016 version.



    Add the table to Pivot data model, it will display the Date type default.



    Create a pivot table, I get the desired result.



    Where do your resource date locate? 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 17, 2017 2:47 AM
  • PowerPivot only has a fixed set of formats and [h]:mm:ss;@ is not one of them.

    However if you convert this column to a decimal you will see that it becomes a number where the integer portion is the total number of days and the decimal portion represents partial days. So 36 hours is 1 day and 12 hours and becomes a value of 1.5. You can then convert this back to a string representation using a formula like the following:

    FormattedTime:=format(int(Sum([Time]),"0") & ":" & FORMAT( Sum([Time] - int(Sum([Time]))*60, "00")

    This is a string measure so it will be left aligned by default, but this is the only way I can think of to get the formatting you are after.


    http://darren.gosbell.com - please mark correct answers

    Friday, February 17, 2017 3:08 AM