yogi_Conditionally Format Cells H5 To P8 -- BackGround Color Based On Priority (Gantt Chart)

                 Google Spreadsheet   Post  #1793

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-13-2014

post by  Thom Flash:

(https://productforums.google.com/forum/#!mydiscussions/docs/kfdeqm54pdk)

Objective: I am building project management spreadsheet like MS Project for tiny house projects, and wish to use changes in date fields to alter colors in a Gantt chart view to indicate task priority and date handling.

Status: Currently, a few columns on the left include tasks and staff assignments, as well as start and end dates. We use the right side of the sheet for a Gantt chart display, with a column for each date in the project life cycle. Conditional formatting changes the cell color for each date in which a "t" is included as a task date. This changes the cell color from the default (white) to a color indicating a date range for task handling (the character "t" and background are changed to same light red). This creates the Gantt chart view.

Sample: Below is a sample of the existing spreadsheet:

(open full-size image)

Process: To simplify management, I'd like for values in Start Date and End Date to automatically change corresponding cell colors in the Gantt chart. In Excel, I've accomplished this using a logical test in each Gantt chart cell to:

1. Get it's date from the timeline at the top of the chart, then
2. Check for "true" to test that the cell's date is "greater than or equal to" the Start Date field, and
3. Check that the cell's date is also "less than or equal to" the End Date field. 

If the above test is true (maybe AND formula?), I figure I can post a character ("t" for general tasks, though others are used to indicate task priority) in the cell which enacts automatic Conditional Formatting to create a colored cell. 

Any help with a simple and scalable solution is greatly appreciated. Thanks!

Thom Stanton

TimberTrails.TV

Timber Trails provides an empty canvass for tiny house builders via efficient, affordable, and easy-to-assemble modular SIP shells with field-friendly how-to media and support materials for building pros and DIY homeowners. Our affordable Fast-Track Design Services utilize an "adaptive design" process that slims down your favorite styles to smaller sizes while retaining real-life practicality. Turn-key customers leverage the advantage of building with SIPs, including their manufactured quality, ease of DIY construction, and incredible energy efficiency. Through a global distribution network, customers can purchase Ready-to-Raise kits, Rolling Models, and Custom Creations. All finish work is completed by independent homeowners and building trade professionals as time and budget allow. Got any other questions or want to get started on your design in a way that’s fast and affordable? Contact us to start building your one-of-a-kind tiny house, mobile cabin, or cozy cottage today. Live Large -- Go Tiny! > >  TimberTrails.TV

---

Yogi - Here's a link to a publicly shared sample of the file: 

https://docs.google.com/spreadsheets/d/1toPXBLx24vHF8YH84_D3WFi28bJ2YqlZxNhUlSnRfjo/edit?usp=sharing

Regarding your requests:

Goal: Cells recognize their date (equal column headers) and add character from "Priority" column if cell (date) equals or is between Start and End dates. 

Cells: All with dates (right side of sheet for Gantt chart view)

Result: Simulated in cells with colors (which change via conditional formatting)

Here's the formula I've used to try and get things working: 

=IF((H$3>=$C5), AND(H$3<=$D5), $B5)

Thanks again! - Thom [>:-) 

--------------------------------------------------------------------------------------------------------------------------------------