vacation GANTT Conditional formatting

[DUKE888]

Hi All,

I'm working on creating vacation plan Gantt for the team. I'm using conditional formatting to get the gantt working but gantt works for one range of date. Can any body help me in creating a rollup bar for Gantt that displays the bar as we see in Outlook meeting schedule and shows the team member holidays highlighted.

Any suggestion or advice would be appreciated.

Thank You all in advance.

[teylyn]

Hi Duke,

welcome to the forum.

I'm not quite sure I understand what you want to do. You have conditional formatting in place to highlight cells with an "x". What exactly do you want to achieve on top of that?

[DUKE888]

Teylyn,

Thank You for your response. I apologize for my improper description. I have resource name in each row in column A. For each resource name, I have added a table starting column AS where I need to enter their vactation start & ending date. The date should reflect as a gantt chart for each resource name. I was able to get the first date range as a gantt but not the rest of'em. I tried to use offset in conditional formatting, but couldn't succeed as excel doesn't allow to use offset in conditional formatting.

For the sake of simplicity, I have deleted conditional formatting and the formula in the attached file and used color code which might explain well.

Thanks Again

[teylyn]

Hi Duke,

see what you can do with the attached.

The formula is too complex to be put into conditional formatting, so we need a two-stepped approach.

- first create the xxxx in the gantt chart with an array formula
- then conditionally format the xxxxx to appear as colored bars

In cell G7 enter this formula

=IF(MAX((G$5>=$AS$7:$AS$13)*(G$5<$AT$7:$AT$13))>0,"x","")

this is an array formula and must be confirmed with Ctrl-Shift-Enter
then copy the cell right

In G8 use this formula

=IF(MAX((G$5>=$AW$7:$AW$13)*(G$5<$AX$7:$AX$13))>0,"x","")
again, confirm with CSE and then copy right. The only difference to the formula in G7 is the range highlighted in red above.

copy the formula down column G and adjust the ranges to match the respective person, then confirm with CSE and copy right across the other columns.

Now you will see xxxxxxxx bars appear in the gantt chart. Next these will be formatted to appear as color bars.

Select G7 to AP7 and use this formula for conditional formatting Formula is

=G7="x"

in G8 to AP8 use

=G8="x"

Note the lack of $ signs in the cell reference. It must be relative to color individual cells.

Set the format for row 7 to be yellow text on yellow fill when the condition is met, and row 8 to be orange text on orange fill.

In the attached I've used the array formula in rows 7 to 10 and color formatted rows 7 and 8. I'll leave the rest to you to play with.

let me know how you get on.

cheers

[contaminated]

Actually, IMO if u want to work with Gantt chart, u can use MS Project . This is very powerfull application.... imo

[teylyn]

Unfortunately, MS Project is not included in the plain vanilla MS Office package, and many users don't have access to it. MS Project does not have the monopoly on Gantt charts, just because that's what most people visualise when they think of the application. Furthermore, a simple vacation planner would be absolute overkill in MS Project, and setting up a chart like the one discussed in this thread would require jumping through some very high hoops in Project. "imo"

[contaminated]

Don't know, I've never used Project (as usually i open this application and clicking some buttons trying to figure out why they are).
But girls in our office make super projects overview and things like that... BTW, I make smth using charts... don't know how it is...

[DUKE888]

Teylyn and Vusal,

I individually THANK YOU both of you for all your effort. Teylyn is right with the fact that MS Project is not given to all as it cost for licensing to use and Excel could be a great handy tool to create an application.

Vusal, Thank You for sharing an additional thought.

Your insights are helping this community.

Cheers,
Duke