Formula to calculate time difference between collected by column

[Kumar Shanmugam]

If I sort Collected by column column Collection dtm column gets unsorted. Hence require solution to calculate time difference between two dateandtime field of collected by person.

Sample data attached for your reference.

 

[vletm]

Do select columns from A to D before You will 'Sort'?
... and You can sort that data by max four levels.
Could You show sample data before 'sort'?
and
Same data after Your sort?

 

[Chihiro]

Something like attached?
Formula in B2: Copy down
=IF(COUNTIFS($C$2:C2,C2,$D$2:D2,D2)>1,A2-INDEX($A$2:$A$11,SMALL(IF(($C$2:$C$11=C2)*($D$2:$D$11=D2),ROW($D$2:$D$11)-1),COUNTIFS($C$2:C2,C2,$D$2:D2,D2)-1)),0)

Confirm as array (CSE).

Note that you may need additional check if data spans multiple days.

Edit: Alternate using IFERROR (Confirm as array)
=IFERROR(A2-INDEX($A$2:$A$11,SMALL(IF(($C$2:$C$11=C2)*($D$2:$D$11=D2),ROW($D$2:$D$11)-1),COUNTIFS($C$2:C2,C2,$D$2:D2,D2)-1)),0)

 

[bosco_yip]

Try……….

In B2, formula copy down :

=IF(COUNTIFS($C$2:C2,C2)>1,A2-LOOKUP(9^9,A$1:A1/(C$1:C1=C2)),0)

Regards
Bosco

 

[Kumar Shanmugam]

Hi thank you all for your prompt response. Chandoo.org forum rocks.

Hi I got the result with the formula of Bosco - =IF(COUNTIFS($C$2:C2,C2)>1,A2-LOOKUP(9^9,A$1:A1/(C$1:C1=C2)),0)

Please find attached the result. Only problem is that formula processing took so much time that I have to close the excel file and reopen it. Finally I did the auto fill formula in little parts I to 5000, 5000 to 10000, 10000 to 15000 rows and so on. and pasted the values of the formula in adjoining column copied the result and pasted it in source data.

but I am not replace the formula with values. Microsoft excel is not responding when we execute the result in one shot and replace the formula with values. Please find attached the desired result.

But Thank you very much All for valuable help.

 

[Chihiro]

Do you have access to PowerPivot & PowerQuery?

With use of multi-level sort, Indexed Column (in PowerQuery) and DAX expression (in PowerPivot). You can accomplish this operation faster and more efficiently.

 

[Chihiro]

Alternately using vletm's suggestion on multi-level sort. Using Excel Table.

See attached.

 

[Kumar Shanmugam]

Hi Chihiro, I don't have access to PowerPivot & PowerQuery?

Yes vletm's suggestion on multi-level sort. Earlier I had a view that it would give a different result in timediff. Now its same. Issue solved. Thanks all.

 

[Kumar Shanmugam]

Hi Chihiro, Can you help me to decipher the formula shared in the previous forum. =IF([@[collected_by]]=B1,[@[collection_dtm]]-A1,0) for calculate time difference between collected by column

Please find attached the sample file .

 

[Chihiro]

So =IF(logical check, calculation if true, calculation if false)

Logical check: Checking that current row value of "collected_by" column is = to row 1 above current.

calc if true: subtract current row value of "collection_dtm" from row 1 above current.

calc if false: zero as false on logical check indicates that it's different set and should start over.

For syntax on structured table reference, see link.
https://support.office.com/en-us/ar...l-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e