How to skip color Cotting for weekends and holidays and continue with next weekdays in Gantt chart

Question

I would like to know how to skip color for weekends and continue color for weekdays in the Gantt chart excel sheet.

Please provide sample formulas to achieve this activity.

Answer 1

Hi @Prabhakar Mekala,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, to color weekdays only, you can try using this set up:

• In my testing worksheet, I create a Gantt chart structure like below:

• To count and color only Weekdays as Workdays, I select the range E2:P2 > go to Conditional Formatting > New Rule > Use a formula to determine which cells to format > use this formula: =AND(E$1>=$B2,E$1<=$D2,WEEKDAY(E$1,2)<=5)
• Then format the cell color as you prefer (I use dark blue for example) and press OK.

• If you want to color weekends as different color than blank, you can add a new rule with the same step and use this formula: =WEEKDAY(E$1,2)>5
• As a result:

I hope this helps you move forward smoothly. If you have any questions or need further assistance, please feel free to reach out again on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To skip coloring for weekends and holidays in a Gantt chart in Excel while continuing with colors for weekdays, you can use conditional formatting with a formula. Here’s a general approach you can follow:

1. Select the range in your Gantt chart that you want to apply the formatting to.
2. Go to the Home tab on the Ribbon, click on Conditional Formatting, and then select New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the following formula (assuming your dates start in cell A1):

      =AND(WEEKDAY(A1, 2) < 6, NOT(ISNUMBER(MATCH(A1, Holidays!$A$2:$A$100, 0))))
   

   • This formula checks if the day is a weekday (Monday to Friday) and not listed in the Holidays sheet (adjust the range as necessary).
5. Click on Format and choose the fill color you want for weekdays.
6. Click OK to apply the rule.
7. You may need to create a separate rule for weekends if you want them to have no color:

      =WEEKDAY(A1, 2) >= 6
   

   Set the format to no fill for this rule.

By applying these rules, your Gantt chart will skip coloring for weekends and holidays while continuing with the specified colors for weekdays.