Gantt Chart Help

TIA for the help


I am using a premade Gantt Chart to track training that will be done for the year. I made some changes to it but I have a couple things that I cannot figure out and in need of help if possible.

1. I would like to remove the weekend days from it if possible or maybe only highlight Monday - Friday
2. When I input time in a box, it does not scroll like the date and day does on the top

#1 just needs some amended formula's to the header row and 1st of month
see attached how it can be done


#2 dont understand the request

just need to remember because your inputting directly into the table (rather than using a formula to draw information into table)
any attempts to shift the headers will result in incorrect data
that is why the highlighting works but the data will suffer misalignment

Originally Posted by humdingaling

#1 just needs some amended formula's to the header row and 1st of month
see attached how it can be done


#2 dont understand the request

just need to remember because your inputting directly into the table (rather than using a formula to draw information into table)
any attempts to shift the headers will result in incorrect data
that is why the highlighting works but the data will suffer misalignment

Thanks for solving #1 humdingaling, appreiciate it very much. Looks like the CF is not coloring the amount of days completely 4/22 did not highlight blue

my thoughts on #2 is to have a rolling calendar that shows the associates that were trained that year to have history to go back on. Some will be in training for 12 weeks max. I wanted to have a start date and end date goal and below that an actual to show if the person is on track or not due to possibly not training on a day they were suppose to or not the full day also. Example: goal was set today to start training for 7:30 but person only got trained for 4:00 so now they are not meeting the goal deadline. Hope this makes sense

#1
did not alter the CF conditions, didn't really look into it
it was working based on the old data set

amend CF
Green
=AND($C8="Actual",J$5>=$F8,J$5<=$G8)
Apply to =$J$8:$BM$39

Blue
=AND($C8="Goal",J$5>=$F8,J$5<=$G8)
Apply to =$J$8:$BM$39

Grey
=AND(LEN($C8)=0,J$5>=$F8,J$5<=$G8)
Apply to =$J$8:$BM$39

Data bar
apply to =$E$8:$E$39

all is fixed on v2 file

PS you need to be very careful when doing cut and paste with spreadsheets full of conditional formats, it can get out of hand very quickly

Re:#2
sorry still dont get it,
the way i generally see scroll bar being implemented in this type of sheets is to increment the dates back and forth...generally skipping 1 month at time rather than 1 day in your example

however you seems to be commenting on TIME (like half day or hours used within a day)
which really doesn't seem to fit the use of scroll bar

it also complicates how the CF would color the square (potentially color half a square is half days hours is used??) and generally how the whole sheet even calculates

the whole basis of your current setup is full days, to break it down into hours. you will have to reconfigure the whole sheet work in hours instead and that would be a rather large job to do

Thanks again humdingaling for everything. I started working on another sheet and changed up a little from this one.


Sorry for not explaining good enough and maybe Excel is not advanced enough to do what I am expecting. As I input hours in the cells under that day I was hoping as I changed lets say months, it would clear what was there. If I went back to the previous month, the information would show again.

This is a newer version I am working on


For the month of January, I have hours inputted in cells and also the days highlighted from the CF set up. When I change the month to February, the cells highlighted from the CF go away but the hours I inputted stay. I was hoping that would go away also but if I decided to go back to January, the information would be there again like the CF highlighted cells do

hhmmm your newest version doesn't take any of the CF fixes i added in, instead resorting back to v1 CF

i guess words i mentioned about inputting directly into table didn't quite make sense to you in previous posts
see attached example of what i said instead, it should be apparent really quickly
i selected FEB on the drop down.. select back to Jan and/or March to see the thing in action

ps side note
if goal is 6 hours and actual is 6 hours, the CF bar will show as 100% ...this makes sense to me

however your progress formula seems to be doing % out of total hours in day
ie if number of days = 3 ...to get 100% (in formula) would be do 21 hours (3 x 7hours)

i am not sure if this is what you want...
pps shouldn't Goal always be 100%? more than 100% of goal means the goal is wrong?

also i change your total formula down the bottom to something easier to manage
replacing your multiple cell references to 2 single sumif formula

kind words
just paying it forward, giving back to the community whom helped me learn much about Excel

well if you are going to set Number of hours per day you can actually just make the data entry a whole lot easier

ie GOAL = 2 hr per day
then you make the table automatically fill in 2 hrs for each day that falls into date range
this will mean that goal is ALWAYS 100% ...which is should be imo
having a goal with 111% does not make sense

then it will make the data entry for ACTUAL even easier

How can all these formulas be protected so no one deletes them behind the scenes but still able to input where need be?

Worksheet Protection
hhmmm this is probably better served by a guide as there a few steps which pictures will be easier to see

https://www.excel-easy.com/examples/protect-sheet.html
you can start from here but i recommend the whole guide

i protected the journal sheet tab but with no password
just click review tab and click protect sheet to unprotect

i suppose that can be the difference between GOAL and ACTUAL?

anyways just throwing it up there and things you can do/try

Originally Posted by humdingaling

i suppose that can be the difference between GOAL and ACTUAL?

anyways just throwing it up there and things you can do/try

Hey humdingaling, sorry I was not clear, but I would need to be able to input time in the cells where a formula you created is set for goals possibly and also actual.

I am guessing I have to input the information on the data sheet also to capture the time.

get an #n/a result for actual when I type information and the actual cells are not completely filled out

change the formula to insert 0 when no matches are found

Formula for K17

Please Login or Register  to view this content.

ps in case your thinking it
Inserting public holidays (or just days off in general) into the mix is possible but it will take up complexity another notch

Is it possible without making this too busy to be able to keep the Goal and Actual percentages as you change months?

This depends on how you want it to actually calculate

right now you are summing the total of what is being shown, so if you move months ...your total changes
ie if you change month to June = 80 x 2 = 160 (100%)
however since you have moved to June you cut out half of April and all of May = 90 (56.25%)
basing on the fact that goal should always be 100%

Progress formula in G16 can now just be

Formula:

Please Login or Register  to view this content.

the other formula for actual is a bit more tricky
however basically just sumifs based on information in DATA tab

Formula:

Please Login or Register  to view this content.

i tweaked your actual formula slightly too...see if it does what you want
instead of false...it should just come as 0 if you have a line but no actual number and just "" if nothing matches at all

not a problem, as long as you are picking up things

Want to figure out also that if goal is at lets say 3 hours a day but the trainer only trained them for 1 hour that box highlights orange or so as visual that they are falling behind to complete goal

Where do you want the orange?? on the progress % or per day in the look up section?

ps avoid quoting the whole post...just quote the bits you want to express
it keeps the posts cleaner

Where do you want the orange?? on the progress % or per day in the look up section?


Both would be awesome if possible and if not, per day I think would work as a visual. Cannot thank you enough for all the time you are spending helping me out.



Also I just noticed when I protect the sheet, it will not let me use the drop down box.

the cell (A10) which your drop down is linked too is protected
just unlock that cell and drop down should work

hmm on progress bar ... its tricky
but it can be done...under Conditional format formula

Formula:

Please Login or Register  to view this content.

applies to
=$G$16:$G$189

for the day by day...ill have a look later in the day

let me expand

you need to unprotect the CELL which the the drop down is connected to
in this case it is cell A10
YgeEkgZ.jpg

does the new version not work?

that's not a problem

i had to do a double take to figure it out myself

now for your conditional format to highlight orange on the date
use this formula

Formula:

Please Login or Register  to view this content.

apply to

Formula:

Please Login or Register  to view this content.

put this as top priority...so orange comes before the Green

ps...this slows your sheet down a little more now when you change the drop down...
just because the extra calculations it needs to do

you can choose to amend your CF formula and only apply to the lines it should ...it should be faster..but thats coding you can try
it just opted for the easy out (in terms of coding and application)

not a problem at all

hope the spreadsheet serves you well

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

should you hit a speed hump in future, just start another thread
there are plenty more skilled individuals on this forum whom can help

the trick is asking the right question

added in column into Data tab for GOAL

Rejigged how GOAL line works
it now searches for a match in DATA tab

if line does not exist...will take the set hr per day
if it is blank (finds error in lookup)...will take the set hr per day

only if number is in there it will take the number

This thread is marked as solved - if more help is required, the SOLVED tag should be removed for now.