Column chart showing activity over 24 hour period

Hello,

This might be a simple solution for Excel gurus, but alas that is not a name that can be applied to me.

I haven't been able to find a solution (manually or online), so thought I'd post a question here.

I'd like to create a column chart to show what activites have been carried out over a period of 24 hours.

i.e.
Activity | Start Time | Finish Time
Sleep 00:00 07:00
Awake 07:00 08:00
Eat 08:00 08:30
Travel 08:30 09:00
Work 09:00 17:00
Travel 17:00 17:30
Exercise 17:30 18:30
Eat 18:30 19:00
Awake 19:00 21:00
Sleep 21:00 24:00

I'd love to display the info horizontally in a stacked column chart, where the 24 hour period would go on the x-axis and the 7 days of the week would be on the y-axis. The data itself would distinguishable through colour/legend/labels.

I thought I'd need to calculate duration of each activity, which in theory may work, but have no idea how to tweak the graph settings so it will display it the correct way (i.e. the blooming clock times).

Thank you for reading/responding.

Regards,
Sam.

Hi Sam,

Welcome to the forums,

Please check out this posting. It has a step-by-step and a video tutorial:

How-to Make a Weekly 24 Hour Time Worked Gantt Chart in Excel

Please let me know if this helps you build what you wanted.

Steve=True

Hi Steve=True,

Thanks for taking the time to reply.

I looked on the link you gave, not sure if it's my browser (IE) but the video is nowhere to be seen.

I followed the instructions (very easy to follow, thanks for making them "casual excel user" friendly)

Your example works for one activity, whereas I need to show multiple activities. They also don't necessarily take a full hour (ideally I'd like to show ¼, ½, ¾ hr).

Hmmmm.

Any thoughts on some tweaks I could make?

Or maybe it needs a different approach?

Hi zoobaby,

Sorry, here is the url, not sure why it is not on the post: http://youtu.be/RP-cRxGKby4

For 1/2 hour or 1/4 hour, you should just add more columns. One for each 1/2 hour if that is what you are using plus one for each fill.

If you are using 1/4 hour, create a column for each 1/4 hour and an fill for each.

Steve=True

I actually found the youtube link

OK, making more columns for smaller timeframes makes sense.

I still can't get my head around making it work for multiple activities though.

Okay ZoooBABY!

Check this out.

I added another column for task name after Day

I added another row per day for the new task.

TimeGanttChart-MultiTask.xlsx

Let me know if this works.

Steve=True

Many thanks Steve for trying to help and spending time tweaking your excellent gant chart to display multitasking.

I sat down and concentrated last night and have found a solution that works in my particular situation. I'm rather more simplistic than you - not sure if that's a good thing or a bad thing though

I've attached a jpg of what I created in case anyone in the future wishes to produce something similar.

First, I made a simple table with start and finish times using basic excel commands (i.e. automatically generating start time as whatever was entered as finish time, that kind of thing). Note that these columns were formatted to show time. Then I made a duration column (again very simple e.g. "B3-A3" to calculate cell D3).

Create a chart using the data contained only within every third column, the duration column.

The hardest part was getting the chart to display hours. I found some very useful instructions at http://smallbusiness.chron.com/chart...cel-50224.html. Since this only displayed 3-hourly intervals, I therefore made the major unit 0.04169 instead of the suggested 0.125 which worked very nicely. The only downside was the loss of the 24:00 label at the end (but I can live with that).

So a very simplistic solution, and obviously helped by the fact that I want to display a full 24 hour period. But, then I suppose you might be able to play with the axis properties if you only wanted to show a portion of the day.

Hope this helps.