Spreadsheet: 846423d1697633410-conditional-formatting-between-times-dts-in-progress
Sheet: Sheet2

 | CO-WORKER | STATE | STATUS | START | END | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00
 | [NAME] | QLD | AVAILABLE | 07:00:00 | 17:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  | STATUS |  | STATE |  | Start | END
 | [NAME] | NSW | AVAILABLE | 07:00:00 | 17:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  | SICK |  | QLD |  | 07:00:00 | 12:00:00
 | [NAME] | QLD | AVAILABLE | 07:00:00 | 18:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  | LEAVE |  | NSW |  | 07:30:00 | 12:30:00
 | [NAME] | QLD | AVAILABLE | 07:00:00 | 17:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  | RDO |  | VIC |  | 08:00:00 | 13:00:00
 | [NAME] | WA | AVAILABLE | 07:00:00 | 16:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  | AVAILABLE |  | WA |  | 08:30:00 | 13:30:00
 | [NAME] | NSW | AVAILABLE | 07:00:00 | 17:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  | PHONE |  | TAS |  | 09:00:00 | 14:00:00
 | Bertha | NSW | AVAILABLE | 07:00:00 | 16:00:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  | WA |  | 09:30:00 | 14:30:00
 |  |  |  |  |  | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  | ACT |  | 10:00:00 | 15:00:00
 |  |  |  |  |  | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  | NT |  | 10:30:00 | 15:06:00
 |  |  |  |  |  | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  |  |  | 11:00:00 | 15:30:00
 |  |  |  |  |  | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  |  |  | 11:30:00 | 16:00:00
 |  |  |  |  |  | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  |  |  | 12:00:00 | 16:06:00
 |  |  |  |  |  | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 | 15:00:00 | 15:15:00 | 15:30:00 | 15:45:00 | 16:00:00 | 16:15:00 | 16:30:00 | 16:45:00 | 17:00:00 | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 |  |  |  |  |  |  |  |  | 16:30:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17:00:00
 |  |  |  |  |  | EVERY CELL ABOVE HAS THE RELEVANT TIME |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17:06:00
 |  |  |  |  |  | Conditional Formatting to allow the following: |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17:30:00
 |  |  |  |  |  | G - BB CELLS TO HIGHLIGHT ACCORDING TO TIME RANGE IN THE E AND F COLUMNS… |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 18:00:00
 |  |  |  |  |  | AND TIME RANGE TO BE CERTAIN COLOUR BASED ON STATUS IN D COLUMN |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 18:30:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 19:00:00
 |  |  |  |  |  | example: [NAME] = AVAILABLE  9am - 5pm … |  |  |  |  |  |  |  |  |  |  |  |  |  | O3-AT3 to highlight yellow |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 19:30:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 20:00:00
 |  |  |  |  |  | *=IF(AND(D3="AVAILABLE", (HOW DO I ADD A RULE TO HIGHLIGHT THE HOURS BEING WORKED BASED ON THE START AND END TIMES)