Gantt Chart

[gr8dane0]

I have created a gantt chart that has a list of employees on along with their start time and duration of their shift. I have conditionally formatted the cells to change color and add a unit value of '1' to the true cells(this would be the time of their shift. However, those that start at 9:00 PM will show their value from 09:00 PM until midnight but the beginning of the sheet will not show the shift. the range is from midnight until midnight.


any help on why the sheet does not recognize the person wraps around after midnight ?

 

[shemayisroel]

Hi & Welcome,

Try and see if the below helps.

http://www.mrexcel.com/articles/gantt-chart-with-conditional-formatting.php

 

[gr8dane0]

Here are my two conditions...
=AND(N$2>=$C3,O$2<=$E3)
also
=OR(C3="",E3="") :this one is so I don't have that pesky #value pop up.

the cell range is a 24 hour period at 15 min increments. with approx 42 individual employees on the left and their start time and scheduled # of hours.

those that start at 9 pm don't wrap to the beginning of the sheet.

 

[T. Valko]

Here are my two conditions...
=AND(N$2>=$C3,O$2<=$E3)
also
=OR(C3="",E3="") :this one is so I don't have that pesky #value pop up.

the cell range is a 24 hour period at 15 min increments. with approx 42 individual employees on the left and their start time and scheduled # of hours.

those that start at 9 pm don't wrap to the beginning of the sheet.

I take it that C3 = start time and E3 = end time.

So, where are the time headers?

 

[gr8dane0]

correct

 

[gr8dane0]

row 2 is the time headers...

 

[T. Valko]

row 2 is the time headers...

OK, is the first time 12:00 AM and then it increments 15 mins each successive column until the last column which would be 11:45 PM ?

That's a lot of columns for a Gantt chart!

 

[gr8dane0]

Yes. Good thing I have three screens to spread it across LOL

 

[T. Valko]

Yes. Good thing I have three screens to spread it across LOL

Here's a small sample file that demonstrates this.

Gantt_Chart.xls 17kb

http://cjoint.com/?1dzqkdSI8LI

How it works...

The first "X" will appear at the header time that is greater than or equal to the start time. For example, start time = 12:08 AM. The first "X" will appear at 12:15 AM.

The last "X" will appear at the header time that is the latest time that is less than the end time. For example, end time = 1:00 AM. The last "X" will appear at 12:45 AM.

If the start time is 12:08 AM and the end time is 12:15 AM there will be no "X's". Who works that shift?

 

[gr8dane0]

Alright,

Here is the actual gantt chart.
As you can see, once the conditional formatting has formatted a series of cells, i.e., employee 1's timeframe. It will not format the cells once the time frame for the employee rolls around again.

Or if we jump to an employee that starts at say 21:00, then the formatting does not recognize that he is already working when the sheet starts at 00:00.

thanks...

public.me.com/gr8dane0
the name of the file is testgantt