How to create a bar chart of hours worked by day?
 

Excel 2003. I have a spreadsheet containing the columns: Date, Time In, Time
Out. E.g.,
5/11/05 08:30 17:30
5/12/05 10:30 18:00

Is it possible to create a chart from this data with the following
characteristics? If so, how?

1. Dates down the left side.

2. Across the bottom, a time-of-day scale, beginning at 0800 and ending at
2200, with labels at one-hour intervals. I.e., 0800 0900 1000 1100 ...
2200

3. For each date, a horizontal bar which begins at the Time In value for
that date and ends at the Time Out value for that date.

Optionally:

4. Ability to enter multiple, non-contiguous Time In/Time Out pairs for a
given date, resulting in a "broken" bar for that date. E.g., 5/12/05 08:30
10:30 12:00 18:00

5. Display of a "total time in" value, in hours to one decimal position, to
the right of each bar. E.g., 8.5.

Thanks!

Hi,

Seems like you need a gantt chart. See Jon Peltier's article.
http://pubs.logicalexpressions.com/P...cle.asp?ID=343

He also has a collection of links here.
http://peltiertech.com/Excel/Charts/GanttLinks.html

Cheers
Andy

ps: apologies if this is a double post my email was playing up :(

mandruss wrote:
Excel 2003. I have a spreadsheet containing the columns: Date, Time In, Time
Out. E.g.,
5/11/05 08:30 17:30
5/12/05 10:30 18:00

Is it possible to create a chart from this data with the following
characteristics? If so, how?

1. Dates down the left side.

2. Across the bottom, a time-of-day scale, beginning at 0800 and ending at
2200, with labels at one-hour intervals. I.e., 0800 0900 1000 1100 ...
2200

3. For each date, a horizontal bar which begins at the Time In value for
that date and ends at the Time Out value for that date.

Optionally:

4. Ability to enter multiple, non-contiguous Time In/Time Out pairs for a
given date, resulting in a "broken" bar for that date. E.g., 5/12/05 08:30
10:30 12:00 18:00

5. Display of a "total time in" value, in hours to one decimal position, to
the right of each bar. E.g., 8.5.

Thanks!




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

You could also just do the said Gantt chart with an Excel table, with
conditional formatting to highlight times which come under different
categories, designated by a letter or number in each cell. However it seems
one can only get 3 conditions to work for each cell.

It is also possible to write a fancy macro reading colours in the 'working'
cells and counting them up - but I'm still figuring out how to do macros with
colours...

"mandruss" wrote:

Excel 2003. I have a spreadsheet containing the columns: Date, Time In, Time
Out. E.g.,
5/11/05 08:30 17:30
5/12/05 10:30 18:00

Is it possible to create a chart from this data with the following
characteristics? If so, how?

1. Dates down the left side.

2. Across the bottom, a time-of-day scale, beginning at 0800 and ending at
2200, with labels at one-hour intervals. I.e., 0800 0900 1000 1100 ...
2200

3. For each date, a horizontal bar which begins at the Time In value for
that date and ends at the Time Out value for that date.

Optionally:

4. Ability to enter multiple, non-contiguous Time In/Time Out pairs for a
given date, resulting in a "broken" bar for that date. E.g., 5/12/05 08:30
10:30 12:00 18:00

5. Display of a "total time in" value, in hours to one decimal position, to
the right of each bar. E.g., 8.5.

Thanks!