Would like to show days off in my Gantt chart

[sinspawn56]

Hello Everyone!

I have a Gantt chart that I've created to help myself determine what classroom to use and what trainer to have teach each class that my business brings on. Currently, I have the chart showing me on what day a trainer and room is booked. Typically our classes are 15 days not including weekends, so the chart shows up with 19 days blotted out. What I would like to do is have this chart, account for days off.

I am provided with a plan, and on that plan it includes the start date, graduation date, and days off of the class. The formula I am using is a basically a count if to return a 1 or 0 if that Room/Trainer is booked for any given day. So it typically returns 1's for a 19 day block. I would like to break up that 19 day block so that it shows that the room/trainer isn't working that day if it is their day off between their start and end date of class. I've attached an example of my file (confidential info removed). Let me know what you think or if I can clarify further!

[Richard Buttrey]

Hi,

I can't see any records for days off so it's a little hard to understand how you would reflect this.

However assuming you record these somewhere then perhaps a conditional format that painted the cell background and font colour to white would work.

[sinspawn56]

Sorry. The Days off are listed on the Plan tab in column O. And I will be completely honest with you that conditional formatting formula are not my strong suit. I do appreciate your reply.

[Richard Buttrey]

Sorry. The Days off are listed on the Plan tab in column O

Not on the file you uploaded.
But for example assuming that say 2 June 2016 was recorded in O8 of the Plan sheet, then using Plan column D as a helper column with D8

Please Login or Register  to view this content.

A first conditional format i.e. taking precedence over your existing CF of

Please Login or Register  to view this content.

would work and suppress the day off.

[sinspawn56]

Ah! Ok, I've re-uploaded the file. I must not have saved it when I uploaded the first time. The problem with your suggestion is that I am not provided the dates, just which weekday it is. So it will say Tue/Wed off. So I will need to look at the class start date and the class end date, then figure out which dates within that range are the Tue/Wed and have the formula return a 0 for that day. I am just not sure how to go about adding that into my formula.

[FlameRetired]

Edited for clarity and typos

See if this gets us close.

In order to compute and factor the days off this uses a helper column in Q2:Q11. There is a named range

DaysOfWk in S2:S8. Drop downs in O2:P11 are defined off that range. This formula in Q2:Q11 defines the days off string for NETWORKDAYS.INTL.

Please Login or Register  to view this content.

Column R is the number of days worked in that period.

Please Login or Register  to view this content.

Row\Col	O	P	Q	R	S
1	Days off	Days off
2	Mon	Tue	1100000	15	Mon
3	Tue	Wed	0110000	13	Tue
4	Wed	Thu	0011000	14	Wed
5	Thu	Fri	0001100	14	Thu
6	Fri	Sat	0000110	16	Fri
7	Sat	Sun	0000011	16	Sat
8	Sun	Mon	1000001	16	Sun
9	Mon	Tue	1100000	16
10	Tue	Wed	0110000	16
11	Wed	Thu	0011000	16

Then I made a copy of the original Sheet1. I filled in the blanks under ‘Trainer Availability’ to make this

formula easier to write. It is array-entered in C4 filled down and across to DR27.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then Conditionally Format as before. I only conditionally formatted Trainer 1-3, and left the rest as 1s and 0s.

[Richard Buttrey]

The problem with your suggestion is that I am not provided the dates, just which weekday it is. So it will say Tue/Wed off. So I will need to look at the class start date and the class end date, then figure out which dates within that range are the Tue/Wed and have the formula return a 0 for that day.

You appear to be suggesting that the day off is the same day every week. Is that always the case and is it only ever one day a week?

This wasn't clear in your original.

[sinspawn56]

You appear to be suggesting that the day off is the same day every week. Is that always the case and is it only ever one day a week?

This wasn't clear in your original.

That is correct. Each class or wave of classes will have the same start date, but could potentially have different days off. But those days off are the same for each week of the 3 week long class. For example if I have two classes one starting on June 2nd and the other starting on June 3rd, the class starting on June 2nd would have Tue/Wed off and the 3rd would have off Wed/Thu.

[sinspawn56]

FlameRetired,

It looks like this might do the trick. Give me a few to transition this over to the production environment and do some testing. I have a feeling that I will need to be able to account for US Holidays as we don't typically train classes on those days, but I think I understand enough of what you are doing to add in that part myself.

[FlameRetired]

sinspawn56, Hope all goes well. Come back if more / different is needed. Thank you for the feedback ... and the rep.

[sinspawn56]

Looks like everything is going to work as intended. Thanks for all your help!

[FlameRetired]

You're welcome.