Chart Ideas to Show Date and Time Trends for Multiple Tasks

**Jnehra** · 08-31-2021, 10:45 AM

Hi All

I am looking to make a recommendation to our partners around how often we should be scheduling certain tasks to run based on actual file activity. The attached data shows task runs over the last 2-3 months and there are multiple tasks each running at different times and dates during this period (approx. 20+ tasks). I am looking for a way to show when a specific task is actually processing a file.

I tried a simple pivot table, but it is very long and clunky. I was thinking some kind of scatter chart (something like the image below) but don't really know where to start. Ideally, it would be nice to have a dynamic chart where I can change the view for each task.

excel-time-chart1.png

I have attached the source data to this post. I appreciate any thoughts, suggestions, or help. This forum has always been very kind and generous in doling out help in the past.

Thanks!

**MrShorty** · 08-31-2021, 11:40 AM

I'm not sure I understand exactly what you want, but I made a guess. Your picture shows something plotted against time of day, so I guessed that you want to do the same thing here. My first guess is to plot "how many times each task started/ended/was active at time of day", so I figure I would do something to count how often each task was active at different times.

1) Insert a pivot table using taskname as the column label, start time as the row label, and count of taskname as the pivot values.
2) I select the row labels and group the row labels by hour.

Now I have a pivot table that show me how many times each task started during each hour of the day. I can then add a column, bar, or line chart to see what time of day tasks are more likely to start.

Is that even close to what you really want to do?

**Richard Buttrey** · 08-31-2021, 12:11 PM

Does the attached offer a way forward.

The bulk of the tasks seem to occur after 14:00 (1112 of the 1208 tasks) so I've hidden the columns before 14:00 on sheet 2