| GANTT Chart.xlsx | ||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | ||||
| 1 | ||||||||||||||||||||||||||||||||||||||||||||
| 2 | ||||||||||||||||||||||||||||||||||||||||||||
| 3 | Project Start: | Mon, 02/09/2024 | ||||||||||||||||||||||||||||||||||||||||||
| 4 | Display Week: | 1 | 2 Sep 2024 | 9 Sep 2024 | 16 Sep 2024 | 23 Sep 2024 | 30 Sep 2024 | |||||||||||||||||||||||||||||||||||||
| 5 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| 6 | TASK | ASSIGNED TO | PROGRESS | START | END | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | ||||
| 8 | Phase 1 Title | |||||||||||||||||||||||||||||||||||||||||||
| 9 | Project A | Employee 1 | 50% | 2/9/24 | 13/9/24 | |||||||||||||||||||||||||||||||||||||||
| 10 | Project B | Employee 2 | 60% | 1/11/24 | 30/11/24 | |||||||||||||||||||||||||||||||||||||||
| 11 | Project C | Employee 3 | 50% | 1/12/24 | 22/12/24 | |||||||||||||||||||||||||||||||||||||||
| 12 | Project D | Employee 4 | 25% | 1/4/25 | 24/6/25 | |||||||||||||||||||||||||||||||||||||||
| 13 | Project E | 24/6/25 | 24/6/25 | |||||||||||||||||||||||||||||||||||||||||
| 14 | ||||||||||||||||||||||||||||||||||||||||||||
| 15 | Insert new rows ABOVE this one | |||||||||||||||||||||||||||||||||||||||||||
ProjectSchedule | ||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I4,AK4,AD4,W4,P4 | I4 | =I5 |
| I5 | I5 | =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1) |
| J5:AQ5 | J5 | =I5+1 |
| I6:AQ6 | I6 | =LEFT(TEXT(I5,"ddd"),1) |
| F10 | F10 | =E10+29 |
| F11 | F11 | =E11+21 |
| F12 | F12 | =E12+84 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Display_Week | =ProjectSchedule!$E$4 | I5 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| D7:D15 | Other Type | DataBar | NO | |
| I7:BL15 | Expression | =AND(task_start<=I$5,ROUNDDOWN((task_end-task_start+1)*task_progress,0)+task_start-1>=I$5) | text | NO |
| I7:BL15 | Expression | =AND(task_end>=I$5,task_start<J$5) | text | YES |
| I5:BL15 | Expression | =AND(TODAY()>=I$5,TODAY()<J$5) | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| E4 | Whole number | >=1 |
Hello,
the file attached has conditional formatting based on the current set up of days but really want to change to Months & Quarters in the same type of format.
However every time I even attempt I fail miserably, would anyone please be able to help?
Formula for grey - =AND(task_start<=I$5,ROUNDDOWN((task_end-task_start+1)*task_progress,0)+task_start-1>=I$5)
Formula for purple - =AND(task_end>=I$5,task_start<J$5)
Formula for Red outline - =AND(TODAY()>=I$5,TODAY()<J$5)
I want the date to be Q1 24 etc and columns below reduced to Jan/Feb/March
I know this is awkward and I can't upload the file
