Need to change duration from Week to Month on gantt/excel chart. Everything I'm trying gives me an error alert.

Natalie Callahan 0 Reputation points
2026-06-29T17:41:27.9633333+00:00

I have an existing Gantt chart/excel chart I am repurposing for my boss with a slightly different project focus. Its set to show weekly progress schedule and I want to change it to only see it monthly but to also be able to show the middle of the month as well as the start of the month (essentially bi-monthly). All the directions I find talk about going to "View" and "Gantt Chart". Am I missing an add in or something? How do I change the sheet to track monthly.

The current equation is =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1)

TIA

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

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Hendrix-C 19,975 Reputation points Microsoft External Staff Moderator
    2026-06-29T18:39:24.59+00:00

    Hi Natalie,

    Based on your sharing, your current equation =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1) is returning weekly column dates by creating Monday of each displayed week then adding 7 days per column/week.

    So, to make it monthly or twice monthly, you need to change the timeline header formula. You can try using this formula to replace your current one

    • For twice monthly: =DATE(YEAR(Project_Start),MONTH(Project_Start)+INT((Display_Week-1)/2),IF(MOD(Display_Week-1,2)=0,1,15))
    • For monthly: =DATE(YEAR(Project_Start), MONTH(Project_Start) + Display_Month-1,1)

    Please note that these formulas are provided solely based on your description so if I have misunderstood anything about your situation, kindly notify me and I'll be happy to assist you further.

    For the best possible assistance, please share a screenshot of your current Gantt chart structure and if possible, your expected outcome too. This will help me clearly understand the situation to provide the best approach for your concern.

    Thank you for your understanding and cooperation. I'm looking forward to your reply.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it.

    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.

    Was this answer helpful?

Your answer

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