Calculate difference between two times using Pivot Table Calculated Field

[mouzzampk2014]

Hi, I tried different steps two subtract two times (cell b-cell a) using calucated fieldd and output the total in cell c but my output is totally wrong. Could someone look into this for me. Much appreciated. Shall I upload sample file? Thank you

 

[mouzzampk2014]

exactly the same:

Could you please click on request access and then I will get notification and I will allow. If that's okay?

 

[sandy666]

try to find option: anyone with this link can edit this item or something similar

and don't quote whole post, please!

 

[mouzzampk2014]

I found it on OneDrive please try now [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/x/s!Ah6KmIniO5ZMgY93boeZu5reJP81DQ I am going to read all the forum conditions tonight. You guys are so helpful. And i am sorry for been noob[/FONT]

 

[mouzzampk2014]

mouzzampk2014

Support by PM is not recommended in this forum.
As mentioned by Fluff (post 4) see the forum rules.

M.

Hi Marcelo, thank you so much for reading message. I won't do that again and thanks again

 

[mouzzampk2014]

I hope link is workign this time

 

[sandy666]

first try with PowerQuery

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"SERNO", type text}, {"INITS", type text}, {"STATE", Int64.Type}, {"MAX_CPHIST_TIME", type time}, {"MIN_CPHIST_TIME", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DATE", "INITS"}, {{"Max", each List.Max([MAX_CPHIST_TIME]), type time}, {"Min", each List.Min([MIN_CPHIST_TIME]), type time}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Grouped Rows", "Subtraction", each [Max] - [Min], type duration)
in
    #"Inserted Time Subtraction"[/SIZE]

then create PivotTable from QueryTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DATE[/td][td=bgcolor:#DDEBF7](All)[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]INITS[/td][td=bgcolor:#DDEBF7]Sum of Max[/td][td=bgcolor:#DDEBF7]Sum of Min[/td][td=bgcolor:#DDEBF7]Sum of Subtraction[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ABH[/td][td]

20:49:00​

[/td][td]

22:06:00​

[/td][td]

0.22:43:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BR[/td][td]

07:25:00​

[/td][td]

20:32:00​

[/td][td]

0.10:53:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]STF[/td][td]

16:10:00​

[/td][td]

04:25:00​

[/td][td]

0.11:45:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

20:24:00​

[/td][td=bgcolor:#DDEBF7]

23:03:00​

[/td][td=bgcolor:#DDEBF7]

1.21:21:00​

[/td][/tr]
[/table]

 

[mouzzampk2014]

When I copy and past the above I get `[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Expression.Error: We couldn't find an Excel table named 'Table1'.`[/FONT]

 

[sandy666]

check Workbook Queries
in your example file the name of the table is: Table1

if your table has different name simply change name in code

 

[mouzzampk2014]

I changed the Table 1 to Raw_Data_Pivot but now I am getting Expression.Error: The column 'SERNO' of the table wasn't found.

[/URL][/IMG]<strike></strike>

 

[sandy666]

try to adapt from your example file

Usually PowerQuery is not Click&Go (or Copy/Paste)