Hello!
I hope someone can help me with this Excel problem.
I have a table of data with multiple columns, one of which holds a time and date entry. Each of these entries is a time that an operator has logged a test result in our system.
What I'd like to do is filter by product (Column A) and figure out the total amount of time they've been working on that product, from the first piece of data entered to the last.
However, the work spans several days and I don't want the result to include any time which is not during working hours.
I do know the shift pattern on each day of the week.
Is there a way to work this out? I've added an image of a simplified example.
I would like to filter by column A to a single product and know the total working hours on the product using column C.
So for example, if I filtered by product 1, that would leave row 16 as the last entry. C16 - C2 would give me the total elapsed time, but a lot of it would be outside shift ours, e.g. after 10pm or before 6am.
Ideally the end goal will be to have this result for ever unique product in the whole data table.
Thanks in advance.
I hope someone can help me with this Excel problem.
I have a table of data with multiple columns, one of which holds a time and date entry. Each of these entries is a time that an operator has logged a test result in our system.
What I'd like to do is filter by product (Column A) and figure out the total amount of time they've been working on that product, from the first piece of data entered to the last.
However, the work spans several days and I don't want the result to include any time which is not during working hours.
I do know the shift pattern on each day of the week.
Is there a way to work this out? I've added an image of a simplified example.
I would like to filter by column A to a single product and know the total working hours on the product using column C.
So for example, if I filtered by product 1, that would leave row 16 as the last entry. C16 - C2 would give me the total elapsed time, but a lot of it would be outside shift ours, e.g. after 10pm or before 6am.
Ideally the end goal will be to have this result for ever unique product in the whole data table.
Thanks in advance.
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Product | Page | Data Entry Time | ||
| 2 | 1 | 1 | 01/01/22 06:52:00 | ||
| 3 | 1 | 1 | 01/01/22 06:53:00 | ||
| 4 | 1 | 1 | 01/01/22 22:47:00 | ||
| 5 | 1 | 2 | 02/01/22 06:41:00 | ||
| 6 | 1 | 2 | 02/01/22 14:17:00 | ||
| 7 | 1 | 2 | 03/01/22 16:53:00 | ||
| 8 | 1 | 3 | 03/01/22 10:01:00 | ||
| 9 | 1 | 4 | 05/01/22 11:03:00 | ||
| 10 | 1 | 4 | 05/01/22 11:03:00 | ||
| 11 | 1 | 4 | 05/01/22 11:04:00 | ||
| 12 | 1 | 4 | 05/01/22 22:35:00 | ||
| 13 | 1 | 5 | 10/01/22 06:35:00 | ||
| 14 | 1 | 5 | 10/01/22 08:56:00 | ||
| 15 | 1 | 5 | 11/01/22 12:45:00 | ||
| 16 | 1 | 5 | 12/01/22 18:46:00 | ||
| 17 | 2 | 1 | 01/01/22 06:52:00 | ||
| 18 | 2 | 1 | 01/01/22 06:53:00 | ||
| 19 | 2 | 1 | 01/01/22 22:47:00 | ||
| 20 | 2 | 2 | 02/01/22 06:41:00 | ||
| 21 | 2 | 2 | 02/01/22 14:17:00 | ||
| 22 | 2 | 2 | 03/01/22 16:53:00 | ||
| 23 | 2 | 3 | 03/01/22 10:01:00 | ||
| 24 | 2 | 4 | 05/01/22 11:03:00 | ||
| 25 | 2 | 4 | 05/01/22 11:03:00 | ||
| 26 | 2 | 4 | 05/01/22 11:04:00 | ||
| 27 | 2 | 4 | 05/01/22 22:35:00 | ||
| 28 | 2 | 5 | 10/01/22 06:35:00 | ||
| 29 | 2 | 5 | 10/01/22 08:56:00 | ||
| 30 | 2 | 5 | 11/01/22 12:45:00 | ||
| 31 | 2 | 5 | 12/01/22 18:46:00 | ||
Sheet1 | |||||
