auto-populate gantt chart visuals based on start & end

Hi all,

I'm reaching out for a quick hand here: I'm helping the Canadian COVID-19 team with project management for their soon to be released app and I am stuck. This community has always been kind to me so I'm reaching out here first.

I'm looking to minimize effort on team leads and we need them to fill out a gantt chart to help us get a sense for where the work streams are. I have this Gantt template set up. What I want to do is automate as much of this as possible because people are working 16hr days on this. I found this template which shows how to automate it but i can't seem to apply it and i'm running out of time to fiddle.

Moderator's note: The above links are directly into Google Sheets, and I have not evaluated compatibility with Excel. However, I have not moved the thread for now to maximize exposure of the question. --6StringJazzer

My ask is as follows for my sheet:

Based on
- a valide date in Column D
- a duration in days in column E
- a colour chosen from a dropdown in H (yes, colour - Eh =)
- this would be a from a dropdown but i haven't been able to find a list of 20 or so colours to set in the dropdown =\ I'm thinking in the tone range of "dark green 1"

can the following be auto-populated and 'locked' to editing:
- Duration
- Status (I set this as a dropdown but now realize that's dumb)
- % Complete
- have the cells in that row of the actual gantt chart (J - BQ) filled with
- The colour chosen from a COLOUR dropdown if Status in column G is "In Progress"
- If Overdue the colour would be red
- if Complete the colour would be 'dark grey 2

The purpose of this sheet is for me to touch base every 1-2 days with team leads to get a sense for where tasks are, and adjust the expected duration accordingly and have the rest just flow.

I'm not really sure to do with the % Complete row - maybe have this be a function of current date in relation to task duration?

Thanks so much - sorry for the rush here. Need to get this into people's hands today.

Sincerely,



Jay

Sorry to be later than what you need. I hope this still helps.

There is a relationship between start date, end date, and duration. If you set two, the third one can be calculated. Which two do you want to set? In the attached, I assume that you are setting the dates. The duration is calculated as working days and includes the start and end days (but does not take into account holidays. That is easy to do but you would need a list of holidays.)

To do this properly, % complete should be entered based on how much of the work has actually been done. Sometimes people base it on the passage of time, but you can be two weeks into a four-week task but still not have done half the work yet. What are you thinking about how to enter % complete?

Status can be determined automatically based on the other data:
Upcoming: Today's date is earlier than the start date
In Progress: % complete is > 0 and < 100
Complete: 100% complete
Overdue: Today's date is later than the start date and the % complete is 0%

The bit with the colors can be done but it's not automatic unless you use macros. That is, you can have a few colors in the dropdown, but you will need one conditional formatting rule written for each color, and if you decide to change the colors in the dropdown you will have to change the conditional formatting rules to match. My example uses three colors. Dark Green 1 is not a range, so I don't know what colors you are thinking about.

For this all to work, your calendar has to use actual dates, not week and day names. I have put it in based on the first week starting Monday March 30.

I'm glad I was able to help. Stay healthy!