I have set of data that shows a few different things: worker #, task #, task start time, and task finish time. The data looks a little something like this.
worker # task# start time finish time
1 task 1 3:30 3:49
1 task 2 3:30 7:35
2 task 3 3:30 4:17
2 task 4 4:43 5:59
2 task 5 6:13 6:37
3 task 6 3:30 5:26
3 task 7 5:46 7:39
As you'll notice, task 1 and 2 are supposedly started by worker 1 at the same time. If anyone has an idea how to get around this then that'd be appreciated but otherwise I am more concerned about trying to come up with a way to transform this data into a gantt chart by time interval. I rounded all of the time stamps to the nearest 15 minute mark. I would like my gantt chart to look like this below.
Time 3:30 3:45 4:00 4:15 4:30 4:45 5:00 5:15 5:30 5:45 6:00 6:15 6:30 6:45 7:00 7:15 7:30
Worker1 task1 task2 task2 task2 task2 task2 task2 task2 task2 task2
Worker2 task3 task3 task3 task4 task4 task4 task4 task4 task5
Worker3 task 6 task6 task6 task6 task6 task6 task6 task6 task7 task7 task7 task7 task7 task7 task7 task7
My Approach
I was able to come up with a formula that populates the time interval that the task is being performed in so I've made a gantt chart that looks like the below.
The formula is these cells is as follows
=IF('Start Time'='time interval','task #',IF(AND('Start Time'<'next time interval','Finish Time'>'next time interval'),'task #',""))
I went about it this way because I figured there would be a way to then scrunch everything so that it's broken out by worker number instead of task number but instead I've gotten stuck in coming up with a formula to do so.
