If and Weekday

[amcmlt]

I am working on a chart that gives the user the option to include or exclude weekends in the forecasting of a project. Have not been able to write the formula correctly for the option to include the weekends.

The design is simple: User places a "x" in a cell on worksheet #1 , if they want to include weekends in the forecast. If excluded, the cell is left blank. Chart is on worksheet #2 . The formula that I wrote and is not functioning correctly is:

=IF('Entry Sheet'!C19="",Workday($E$6,0,Holidays),IF('Entry Sheet'!C19="x",Workday($E6,0,Holidays)))

The option to exclude weekends & holidays works correctly.

Thank you for your efforts!

 

[James006]

Hello,

In order to include weekends and holidays ... your formula should simply be B2-A2 (end start - start date)

HTH

 

[amcmlt]

Thanks but I think I have a bigger mess that I originally thought. I will try to detail my current situation:

Building a chart that is intended to show time slots for various task in a manufacturing process. Each task is listed in column C, Task duration is listed in Column D as HR:MM, Start time column E is to be entered by the User, Column F is the calculated End time of a specific task, column G is the Start Date of the task which is also entered by the User, column H is the calculated End Date of the task. Row 9 Columns I through AC as dates listed as Start Date and moves out 20 days. Dates are formatted at Friday, October 9, 2018. Rows 11-22 columns I-AC are to display with a X and color from Conditional Formatting, days which production will take place. I am using the Weekdays formula but am having no success writing a formula that will see the dates listed in Row 9 Columns I - AC, and place an "X" in the proper cell based on days of production and whether the User wants weekends included in the graph. I hope this is detailed and logical for a response.

Thanks again!

 

[James006]

Hello again,

Not sure to fully understand ...

Should you need to exclude Weekends with the Weekday() function, you could test following:

=MOD(WEEKDAY(A1,3),7)<5

will produce a TRUE / FALSE for any value in cell A1 ... (FALSE = Saturday or Sunday )

Hope this will help

 

[amcmlt]

Thank you for your input! I have not found the solution to the Gantt chart reflecting the task dates (start / end dates) issue but will continue working on the solution.

 

[James006]

Hello,

Regarding a Gantt chart, everything can be handled with conditional formatting ...

For example, Start Date in cell A2 - End date in cell B2 - and then your headers in Row 1 starting in Column D,

the formula for conditional formatting would be :

=(D$1>=$A2)*(D$1<=$B2)

Hope this will help

 

[ebea]

You can find a good ready made Gantt Chart, where you can see solutions to you'r problem, in the functions in the Chart! https://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html