Excluding weekends from Gantt Chart

[Yannis07]

Hi,

I have posted something similar about this in the past and the formula someone gave me worked wonderfully. However, only I new how to work the chart in the office so it wasn't practical. I would like to do the same with this gantt chart so they don't have to keep amending the timeline dates every time they put together a programme.

Basically we would like to exclude Saturdays and Sundays as working days but be able to add them in when and as we need them. On the last chart I could type a w onto the Saturday or Sunday I wanted to show as a working day and it highlighted that day. I'm hoping something similar is possible with this chart but I can't figure out how to tailor the formula I was given to suit the attached excel chart.

Can anyone help me please?

[Shareez Saleem]

In cell H5,

Please Login or Register  to view this content.

I5 Onwards,

Please Login or Register  to view this content.

[Yannis07]

Thanks Shareez. However, I still want the weekends to show but not highlight as a working day. Then if we need to show that we will be working on a specific weekend we can do so.

Is that possible?

[Shareez Saleem]

So, you just want to highlight Sundays and Saturdays?
If yes, please use conditional formatting.
Select the range and use the formula =OR(TEXT(H$5,"ddd")="Sat",TEXT(H$5,"ddd")="Sun")

[PietBom]

Maybe this works for you.
Changed Conditional Formatting.
Added a few columns and WORKDAY-formulas.

[Yannis07]

Hi, thanks for that but it's not quite what I am trying to achieve. Please see attached our older gantt chart. If you type in dates it excludes the weekends from the highlighted bar, but if you type a 'w' into one of those weekends in the main timeline area it will show that as a working day.

Sorry if i'm not being clear here, it's quite hard to explain what it is I'm looking to achieve.

[kvsrinivasamurthy]

It is not clear where you type W.
Where you are doing calculation taking weekends as holiday or working day.

[Yannis07]

It is not clear where you type W.
Where you are doing calculation taking weekends as holiday or working day.

You type the w actually in the day that you want it to highlight. so for example if i put a task in that lasts 3 weeks, it will automatically exclude the weekends on the timeline. If I wanted to highlight one Saturday as a working day, I would just type a W into that day on the timeline and it automatically highlights that day to show as a working day.

Please see attached. I have typed in the w in the highlighted cell to show that as a working day.

[kvsrinivasamurthy]

I didn't find W.

[Yannis07]

I didn't find W.

In cell N9 I have typed a W which highlights that cell and makes the W appear as a working day

[kvsrinivasamurthy]

What actually required is not clear. Pl explain clearly.

[Yannis07]

What actually required is not clear. Pl explain clearly.

Apologies if I'm not being clear. Basically I want the gantt chart in my original comment to do something similar to the gantt chart last uploaded. When I type in a set of dates, I need it to automatically exclude weekends as a working day (So only highlight weekdays) then if I need to show a weekend as a working day, I can do so by typing a W into that day.

Hope this makes sense

[Yannis07]

Here is the formulas I used last time for the older gantt chart. Although some of the cells may have changed, the formula should still make sense to someone who knows how to read them.

I believe the conditional format for the diagonal black stripes (because I couldn't work out how to get the green ones to show for me) is:-

=AND(OR(G9="w",G$8<>"S"),ActualBeyond)

And, while in cell G9, the formula for PeriodInActual is:

='Project Planner'!G$6=MEDIAN('Project Planner'!G$6,'Project Planner'!$D9,WORKDAY('Project Planner'!$D9,'Project Planner'!$E9-1-COUNTIF(INDIRECT(ADDRESS(ROW(),MATCH('Project Planner'!$D9,'Project Planner'!$6:$6,0))&":"&ADDRESS(ROW(),MATCH(WORKDAY('Project Planner'!$D9,'Project Planner'!$E9-1),'Project Planner'!$6:$6,0))),"w")))

Edit: Adding Explanation
This is essentially what I've added to the formula:
'-COUNTIF(INDIRECT(ADDRESS(ROW(),MATCH($D9,$6:$6,0))&":"&ADDRESS(ROW(),MATCH(WORKDAY($D9,$E9-1),$6:$6,0))),"w")

The green part finds the start date, the blue finds the end date, the pink then counts the number of w's in that range


The above is a comment that someone made to change my original gantt chart

[kvsrinivasamurthy]

I am not clear.
Select D9:AQ20.
Formula for CF

=AND(D$6>=$B9,D$6<=$C9,WEEKDAY(D$6,2)<7)

Fill --> Green

[Yannis07]

I am not clear.
Select D9:AQ20.
Formula for CF

=AND(D$6>=$B9,D$6<=$C9,WEEKDAY(D$6,2)<7)

Fill --> Green

Sorry I think you are referring to my older gantt chart. The chart I am trying to amend is in the original post in this thread. I attached our old chart further down to help you understand what I am trying to achieve.

'Blank Gantt Chart Old' works how I want it to in terms of excluding weekends and only including them when I need to, but isn't practical for my colleagues to amend dates as time goes on.

I want to exclude weekends from the gantt chart attached, and be able to include them as and when I need to. Apologies, it is quite difficult to explain what I mean in text

[Beamernsw]

Hi Yannnis, a simple addition to your conditional formatting will implement that change for you. This is the formula for the Low Risk conditional:

Please Login or Register  to view this content.

OR(H8="W", ... allows any cell with a "W" in it to be highlighted
H$7<>"S") simply adds to ONLY highlight a cell if it isn't on an "S" day (Saturday or Sunday).

So only cells without an S in the day row OR a W in the cell itself will be highlighted.

The problem is, once you enter a "W" into one of those cells, it will wipe out the formula you have in there. But that formula isn't working anyway since you can't enter the words "Goal" or "Milestone" into cells $C10.

[Yannis07]

Hi Yannnis, a simple addition to your conditional formatting will implement that change for you. This is the formula for the Low Risk conditional:

Please Login or Register  to view this content.

OR(H8="W", ... allows any cell with a "W" in it to be highlighted
H$7<>"S") simply adds to ONLY highlight a cell if it isn't on an "S" day (Saturday or Sunday).

So only cells without an S in the day row OR a W in the cell itself will be highlighted.

The problem is, once you enter a "W" into one of those cells, it will wipe out the formula you have in there. But that formula isn't working anyway since you can't enter the words "Goal" or "Milestone" into cells $C10.

You sir are a true gentlemen. Really appreciate that thank you so much!

[miladin]

Hello, does someone has a working template to share ?
With excluded weekends

[FDibbins]

Hello, does someone has a working template to share ?
With excluded weekends

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread