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

 

[sandy666]

are you creating PT from QueryTable or from loaded into the sheet "green" table?

best way is share your original excel file (hope there is no any TopSecret data )

 

[mouzzampk2014]

Nothing important only issue data is connected to SQL Server and if I send you then you won't be able to open I really want to send you its much quicker.

I am right clicking on Workbook Queries and then edit and then Advanced Editor. Am I doing right?

 

[mouzzampk2014]

If I remove SERNO from group. Subtraction start behaving properly

 

[sandy666]

you mean PQ is connected to SQL?

 

[mouzzampk2014]

Yes Excel connected to Excel. Now I found the error. When we add SERNO in the Group then Pivot Table only shows first time and last time for each SERNO. So what I was looking for on 04/02/2019 ABH MIN 10:41 and MAX 14:31 and then Total and how many SERNOs dealt.

I hope that make sense?

 

[sandy666]

I hope that make sense?

I don't know

you said you want to achieve post#26 so I did it (post#27) from shared example.

maybe try get data from SQL , nothing change, load into the sheet, right click on the table then Table - Convert To Range, save and share
and I can treat this as quasi-source from SQL

 

[mouzzampk2014]

I'm just taking little one to school now. Will be back soon. But you done more than I asked and what's the best you done you taught me something new power query and that's what I'm going to focus now you have been so good.

The file I shared with you earlier exactly what I have in original file. Only thing is it's not 700000 entries only few hundreds .

04/02/201 ABH 10:41 14:31 Number of SERNO between those two times.

I hope you figure it out. I'm going to try again and I hope I will come with some idea too

 

[sandy666]

Excel sheet accept ca over 1 million rows so this is not a problem

Total number of rows and columns on a worksheet

1,048,576 rows by 16,384 columns

 

[sandy666]

check this: Excel file or I don't understand what you want to achieve
Be aware there are many duplicates