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.
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).
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.