Friday, 22 March 2013

"Schedule 2" chart

Figure. 1: "Schedule 2" chart.

It is always useful to organize your daily tasks, especially on days that you have a lot of things to do. During the previous week I noticed that there are some days that I have a really heavy schedule, so I tried to help myself organizing the daily tasks. My initial thought was to write the daily tasks in a typical worksheet, as if I had a notebook. However, after creating the worksheet I decided to visualize the schedule in order to have a quick overview of the remaining tasks. Finally, I ended up creating two custom charts that depict the daily schedule.

These two charts are in reality a XY chart and a stacked bar chart, but with many customizations. On the first chart (named “Schedule 1”), I used custom error bars in order to represent the duration of each task (figure 2). On the second chart (named “Schedule 2”), I made some adjustments (hide one series, hide the Y axis etc.) so as to look like a Gantt chart (figure 1). Both charts can show up to 15 different tasks, which in my opinion are adequate for a typical day.

 "Schedule 1" chart

Figure. 2: "Schedule 1" chart.

How to use it

First of all, you must insert the data on the “Input Data” sheet. For each task complete the start time, the duration and the description. Note that you should enter the time in 24 hour format (i.e. start time 17:30, duration 2:00, and description “Meeting”). With these data the charts can be updated. However, within the day you might want to check either how many tasks were finished or how many tasks are pending. In that case, on the column "Done?" you can select “Yes” or “No” from the dropdown list of each task, and, automatically, on the column "Status" you will have the “Pending” and the “Finished” tasks (figure 3).

"Input Data" sheet

Figure. 3: "Input Data" sheet.

The “Input Data” sheet uses the NOW function to determine the actual time whenever there is a workbook change. So, if a task is marked as “Yes” in “Done?” column and the start time plus the expected duration is higher than the time that returns the NOW function, the column “Status” changes to “Finished Earlier”. Moreover, if the previous condition is true, the conditional formatting of the sheet changes the background colors of cells that containing tasks which should have been finished by that time (i.e. if you open the workbook at 20:00, the task “Meeting” should be in light blue color, instead of green).

Finally, in the “Input Data” sheet there is a “Clear” button, that if is being pressed all tasks are deleted. All figures here show – more or less – the tasks that I have to fulfil every typical weekday. As you can see, I have a really crowed daily schedule…

Download it from here

This file can be opened with Office 2007 or newer. Please, enable macros the first time that you run this workbook.

Did you like this post? If yes, then share it with your friends. Thank you!


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info