Hello everyone,
I am constructing a gantt chart from cells in pivot table, I am having trouble with the conditional formatting to highlight cells between two dates,
The cells that say blank, were inputted by excel when creating the pivot table, they do not have dates in them.
I am using a pivot table so i can use slicers to filter what is required, as in metro/regional, locations and so on, i have not included that information with the below minisheet.
Any help will be greatly received,
Thank you
Stefan
I am constructing a gantt chart from cells in pivot table, I am having trouble with the conditional formatting to highlight cells between two dates,
The cells that say blank, were inputted by excel when creating the pivot table, they do not have dates in them.
I am using a pivot table so i can use slicers to filter what is required, as in metro/regional, locations and so on, i have not included that information with the below minisheet.
Any help will be greatly received,
Thank you
Stefan
| For mini sheet.xlsx | ||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | 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 | AR | |||
| 1 | Start Month | April | ||||||||||||||||||||||||||||||||||||||||||||
| 2 | End Month | April | 1-Apr-23 | 8-Apr-23 | 15-Apr-23 | 22-Apr-23 | 29-Apr-23 | |||||||||||||||||||||||||||||||||||||||
| 3 | 1 | 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 | |||||||||||
| 4 | Status | Type | Region | Hospital | Date Received | Start Date | End Date | RTP | PERSON | 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 | M | T | W | T | F | ||
| 5 | Cancelled | Scheduled | Metro | Home | (blank) | (blank) | (blank) | (blank) | SMITH | |||||||||||||||||||||||||||||||||||||
| 6 | Home | SMITH | ||||||||||||||||||||||||||||||||||||||||||||
| 7 | Home | (blank) | (blank) | (blank) | (blank) | SMITH | ||||||||||||||||||||||||||||||||||||||||
| 8 | Completed | Scheduled | Metro | Home | (blank) | 3/04/2023 | 4/04/2023 | (blank) | SMITH | |||||||||||||||||||||||||||||||||||||
| 9 | Home | 5/04/2023 | 6/04/2023 | (blank) | SMITH | |||||||||||||||||||||||||||||||||||||||||
| 10 | Home | (blank) | 4/04/2023 | 4/04/2023 | (blank) | SMITH | ||||||||||||||||||||||||||||||||||||||||
| 11 | Declined | Scheduled | Metro | Home | (blank) | (blank) | (blank) | (blank) | SMITH | |||||||||||||||||||||||||||||||||||||
| 12 | Home | (blank) | (blank) | (blank) | (blank) | SMITH | ||||||||||||||||||||||||||||||||||||||||
| 13 | Full Sit | Scheduled | Metro | Home | (blank) | 11/04/2023 | 11/04/2023 | (blank) | SMITH | |||||||||||||||||||||||||||||||||||||
| 14 | Home | SMITH | ||||||||||||||||||||||||||||||||||||||||||||
| 15 | Home | 28/04/2023 | 28/04/2023 | (blank) | SMITH | |||||||||||||||||||||||||||||||||||||||||
| 16 | Home | (blank) | 21/04/2023 | 21/04/2023 | (blank) | SMITH | ||||||||||||||||||||||||||||||||||||||||
| 17 | Home | (blank) | 14/04/2023 | 14/04/2023 | (blank) | SMITH | ||||||||||||||||||||||||||||||||||||||||
| 18 | Not Completed | Unscheduled | Metro | Home | 11/04/2023 | (blank) | (blank) | 13/04/2023 | SMITH | |||||||||||||||||||||||||||||||||||||
Sheet1 | ||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2,Q2,X2,AE2,AL2 | J2 | =J3 |
| J3 | J3 | =I1 |
| K3:AR3 | K3 | =J3+1 |
| J4:AR4 | J4 | =LEFT(TEXT(J3,"ddd"),1) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| J5:AR18 | Expression | =IF(AND($G$22<=$K$17,$H$22>$L$17),1,"") | text | NO |
