How to combine the WORKDAY function with an IF function to build a working Gantt chart.

Hi there!

I'm currently using the below formula to build a simple Gantt chart in Sheets, which ultimately places an x along my timeline based on the start date, duration (in days), and predicted end date of a task within a project:

=if(and(K$5>=$E6,K$5<=$I6),"x","")

From this, I then use conditional formatting to fill the boxes marked with x, which creates a simple bar chart. In this formula, K is the date along the top of my table (January 1st, January 2nd, and so on) and I is the estimated end date of that specific task.

This is working great for me, but ideally I'd like it to exclude weekends and move to the next working day if at all possible. I believe I need to use the WORKDAY/WEEKDAY function in conjunction with this, but can't figure out where within my formula I need to place it.

Could anybody kindly help? Many thanks in advance.

I think you need WEEKDAY not WORKDAY: https://support.office.com/en-us/art...0-e404c190949a

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.