Forum Discussion
Calculating hours worked over x days excluding down time
Hi! I have a problem that I have been trying to solve for days. I need to calculate working hours by manufacturing line over multiple days. the problem is I have to do this by time of first unit produced-time of last unit produced for each day. I dont know how to go through each day, take the min time and max time, subtract the two, and add to a running total going through everyday that my date slicer has included. If you are able to help that would be awesome!! Let me know
Here is some sample data. This will eventually allow me to calculate an accurate "Units per hour".
so for line 1 it should return (8am-4pm) + (7am-1pm) +(6am-2pm) = 22 hours worked
I will have a table that has line number as the first column and working hours (this output) as the second column and sliced using a calender...
| Line Number | Date | Time | Units |
| 1 | 9/20/2022 | 8am | 1 |
| 1 | 9/20/2022 | 10am | 1 |
| 1 | 9/20/2022 | 1pm | 1 |
| 1 | 9/20/2022 | 4pm | 1 |
| 1 | 9/21/2022 | 7am | 1 |
| 1 | 9/21/2022 | 9am | 1 |
| 1 | 9/21/2022 | 11am | 1 |
| 1 | 9/21/2022 | 1pm | 1 |
| 1 | 9/22/2022 | 6am | 1 |
| 1 | 9/22/2022 | 10am | 1 |
| 1 | 9/22/2022 | 2pm | 1 |
| 2 | 9/20/2022 | 8am | 1 |
| 2 | 9/20/2022 | 10am | 1 |
| 2 | 9/20/2022 | 1pm | 1 |
| 2 | 9/20/2022 | 4pm | 1 |
| 2 | 9/21/2022 | 7am | 1 |
| 2 | 9/21/2022 | 9am | 1 |
| 2 | 9/21/2022 | 11am | 1 |
| 2 | 9/21/2022 | 1pm | 1 |
| 2 | 9/22/2022 | 6am | 1 |
| 3 | 9/21/2022 | 10am | 1 |
| 3 | 9/21/2022 | 2pm | 1 |
| 3 | 9/22/2022 | 9am | 1 |
| 3 | 9/22/2022 | 11am | 1 |
| 3 | 9/22/2022 | 1pm | 1 |
9 Replies
I just realized my date column contains the day and time so the table creates a new row for everytime. how do i create a new column for just the date? I would like to avoid changing my existing date column as i have measures using it.
