Share via

How to adapt a formula to create a gantt chart reflecting days, not weeks

BCW 0 Reputation points
2025-08-27T02:24:46.17+00:00

I am working in an existing Gantt chart file which has been shared with me. It's original function was to round the time between 2 dates to the nearest full week and show this in the Gantt chart. I would like to adapt this formula to represent days, ideally working days, between the same 2 dates and automatically move the Gantt chart bar to reflect this.

The current formula is as below:

=IF(OR(ISBLANK(J10),ISBLANK(Q10)),"",1+ROUND((((Q10-WEEKDAY(Q10, 2)+1)-(J10-WEEKDAY(J10, 2)+1))/7),0))

Thank you!

Microsoft 365 and Office | Excel | For business | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sophie N 2,445 Reputation points Microsoft External Staff Moderator
    2025-08-27T03:03:17.2666667+00:00

    Dear @BCW

    Thank you for posting your question in the Microsoft Q&A forum. I understand you’d like to modify your existing Gantt chart formula to reflect working days (instead of weeks) between two dates. Here’s a solution tailored to your needs: 

    Replace your current formula with the following: 

    =IF(OR(ISBLANK(J10), ISBLANK(Q10)), "", NETWORKDAYS(J10, Q10)) 

    This formula uses Excel’s NETWORKDAYS function to calculate the number of working days (Monday–Friday) between the start date (J10) and end date (Q10). It automatically excludes weekends.

    For more details, see the NETWORKDAYS function documentation. User's image

    1. How It Works: 
      • NETWORKDAYS counts weekdays between two dates (inclusive). 
      • If either cell (J10 or Q10) is blank, the formula returns a blank to avoid errors. 
    2. Customizing Holidays (Optional): 
      If you need to exclude specific holidays, list them in a range (e.g., Z1:Z10) and modify the formula:  =IF(OR(ISBLANK(J10), ISBLANK(Q10)), "", NETWORKDAYS(J10, Q10, Z1:Z10)) 
    3.  Updating Your Gantt Chart: 
      • Replace the existing formula in your Gantt chart bar’s data source with the new formula. 
      • Ensure the chart’s axis/format is set to display days (not weeks). Right-click the axis > Format Axis > Adjust units to days. 

    For detailed guidance, you can refer this article: Display or change dates on a category axis - Microsoft Support

    This adjustment will automatically align your Gantt chart bars to represent working days. If you encounter issues or need further customization, feel free to reply. I’m here to help! 

    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.

     User's image

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.