Gantt Chart using Conditional Formatting and Months as a Scale

[ormont02]

Greetings,

I'm currently trying to put together a Gantt Chart like table to show a schedule of when projects start and stop by month. using Excel 2003. My current data includes the following:

A1: Project Name:
B1: Start Date
C1: End Date
D1 to XX: Jan-10 (in format of 01/01/2010), Feb-10 (in format of 02/01/2010), etc.

I'd like to be able to plug in a Start Date (e.g. 10/09/2009) and End Date (e.g. 01/26/2010), and use conditional formatting to highlight the cells to illustrate the project timeline.

I've seen these and read these related posts:
http://www.mrexcel.com/articles/gantt-chart-with-conditional-formatting.php
http://www.mrexcel.com/forum/showthread.php?t=315573

The concepts illustrated in these posts such as with the formula =AND(D$1>=$A1,D$1<=$B1) works, with one BIG EXCEPTION...if I enter start and end dates such as the following:

Start Date: 05/26/09
End Date: 10/02/09

The only range of cells that would be highlighted using the current formula (noted above) leaves out May and October as months that are included in the schedule of the project.

So, in essence I'm asking if there is a way or formula that I could write using conditional formatting to ensure the month was highlighted even if the date was beg/mid/late of the month.

Hopefully that makes sense and any help is great greatly appreciated!!!

Thanks!!!

ormont02

 

[excelnewb123]

Hi is there any update or answer here for help is greatly appreciated. I'm needing the same answer. thanks!

Greetings,

I'm currently trying to put together a Gantt Chart like table to show a schedule of when projects start and stop by month. using Excel 2003. My current data includes the following:

A1: Project Name:
B1: Start Date
C1: End Date
D1 to XX: Jan-10 (in format of 01/01/2010), Feb-10 (in format of 02/01/2010), etc.

I'd like to be able to plug in a Start Date (e.g. 10/09/2009) and End Date (e.g. 01/26/2010), and use conditional formatting to highlight the cells to illustrate the project timeline.

I've seen these and read these related posts:
Excel | Conditional Formatting | Gantt Chart on a Worksheet
Gantt Chart with Conditional Formating

The concepts illustrated in these posts such as with the formula =AND(D$1>=$A1,D$1<=$B1) works, with one BIG EXCEPTION...if I enter start and end dates such as the following:

Start Date: 05/26/09
End Date: 10/02/09

The only range of cells that would be highlighted using the current formula (noted above) leaves out May and October as months that are included in the schedule of the project.

So, in essence I'm asking if there is a way or formula that I could write using conditional formatting to ensure the month was highlighted even if the date was beg/mid/late of the month.

Hopefully that makes sense and any help is great greatly appreciated!!!

Thanks!!!

ormont02