Answered by:
PowerPivot and time field formatting
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
 Edited by Ahmed M Abdel Kader Thursday, February 16, 2017 8:01 AM
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
 Marked as answer by Ahmed M Abdel Kader Sunday, February 19, 2017 8:15 AM
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,
AngeliaMSDN 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
 Marked as answer by Ahmed M Abdel Kader Sunday, February 19, 2017 8:15 AM
Friday, February 17, 2017 3:08 AM