Hi all,
I have a list of shipping numbers with piece numbers and date/time of first scan. Now i want to know when there is more then 1 hour between different piece numbers of the same shipping number (partial arrival).
As i have more then 100k rows i would like to find a formula in PowerQuery to reduce the stress on excel but all ideas are welcome!
the table should have following outcome.
<tbody>
</tbody>
I was thinking to combine a min, max function combined with countifs but i cant figure it out
in addition i am limited to excel 2013 and the datedif function doesnt seem to be included in this version.
I have a list of shipping numbers with piece numbers and date/time of first scan. Now i want to know when there is more then 1 hour between different piece numbers of the same shipping number (partial arrival).
As i have more then 100k rows i would like to find a formula in PowerQuery to reduce the stress on excel but all ideas are welcome!
the table should have following outcome.
Shipping number | piece number | first scan | Partial arrival? |
123456789 | 2 | 22/06/2019 13:00 | Yes |
456789123 | 1 | 21/06/2019 10:00 | no |
123456789 | 4 | 22/06/2019 13:50 | Yes |
123456789 | 3 | 22/06/2019 14:10 | Yes |
456789123 | 2 | 21/06/2019 10:50 | no |
123456789 | 1 | 22/06/2019 14:30 | Yes |
987654321 | 2 | 22/06/2019 12:10 | Yes |
987654321 | 3 | 23/06/2019 12:30 | Yes |
987654321 | 1 | 22/06/2019 12:50 | Yes |
<tbody>
</tbody>
I was thinking to combine a min, max function combined with countifs but i cant figure it out
in addition i am limited to excel 2013 and the datedif function doesnt seem to be included in this version.