Conditional Formatting Using Date Range (Gantt Chart)

[ACF0303]

I am looking for the best way to use conditional formatting in a Gantt chart. I want the cells to turn blue if they fall between two dates. Any help would be greatly appreciated!

 

[iliace]

Best way costs money. Here's the simple way. In B2, you specify start date, and in C2 you put the number of days.

Below, you list each activity and the begin/end dates. That drives the conditional formatting to create your chart. The conditional formatting applies to the entire range.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

Start Date

Interval

Activity

From

To

Management

Execution

Evaluation

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:89px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]31-Jul[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]31-Jul[/TD]
[TD="align: right"]15-Aug[/TD]
[TD="align: right"]30-Aug[/TD]
[TD="align: right"]14-Sep[/TD]
[TD="align: right"]29-Sep[/TD]
[TD="align: right"]14-Oct[/TD]
[TD="align: right"]29-Oct[/TD]
[TD="align: right"]13-Nov[/TD]
[TD="align: right"]28-Nov[/TD]
[TD="align: right"]13-Dec[/TD]
[TD="align: right"]28-Dec[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]15-Aug[/TD]
[TD="align: right"]30-Oct[/TD]

[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]19-Aug[/TD]
[TD="align: right"]20-Nov[/TD]

[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2-Nov[/TD]
[TD="align: right"]15-Dec[/TD]

[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]
[TD="bgcolor: #0070c0"] [/TD]

</tbody>

Formeln der Tabelle

Zelle Formel D4 =$B$2+$C$2*(COLUMN()-4) E4 =$B$2+$C$2*(COLUMN()-4) <tbody> </tbody>

<tbody>
</tbody>

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format D6 1. / Formel ist =AND(D$4>=$B6, D$4<=$C6) <tbody> [TD="bgcolor: #0070c0"]Abc

<tbody>

[TD="bgcolor: #0070c0"][/TD]

</tbody>

[/TD]

</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

 

[ACF0303]

Thank you very much! I will give this a try as soon as I can. So that I can get better: can you explain what the "COLUMN()
does? I am assuming the -4 tells it to skip the first 4 columns....

 

[iliace]

In column 4, COLUMN()-4 = 0, therefore 15*0 = 0 (interval)
In column 5, COLUMN()-4 = 1, therefore 15*1 = 15
In column 6, COLUMN()-4 = 2, therefore 15*2 = 30

etc.

This calculates the number of days from the project start date, based on the interval value in days. So by changing that and the beginning date, you can automatically zoom in/zoom out on particular date ranges.

By the way, about six months ago I came a cross a pretty cool (free) tool to create these types of charts in Excel. Search for "PapaGantt", you should be able to find it. Might be worth checking out.

 

[ACF0303]

Thanks for all the help and suggestions - worked like a charm!