Formula Errors

How to Change an Excel Gantt Chart Formula from Weeks to Months

Amos Gikunda Oct 1, 2026 868 views

Question details

The user wants to adapt an existing Gantt chart so its timeline progresses in monthly intervals rather than weekly intervals.

How to Change an Excel Gantt Chart Formula from Weeks to Months

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Modifying a project management Gantt chart to reflect a longer-term monthly view instead of short-term weekly increments.
Observed behavior: The current timeline formula calculates and displays seven-day increments, making the timeline too stretched or unsuitable for tracking monthly milestones.

Before you start

Verify the exact cell references for your project start date and ensure you understand how your current conditional formatting rules read the timeline headers.

Solution 1Recommended

Update Timeline Headers Using the EDATE Function

Replace the 7-day increment formula (+7) with a month-adding function like EDATE to shift the timeline to a monthly scale.

By default, weekly Gantt charts simply add 7 days to the previous cell. To shift to a monthly scale, the formula must be redesigned to calculate month start or end dates accurately, accounting for the different number of days in each month.

Locate the start date reference

Find the first date cell in your timeline header (e.g., D4) and ensure it correctly references the start date of your project.

Enter the EDATE formula

Select the next adjacent timeline cell (e.g., E4). Replace the existing +7 formula with =EDATE(D4, 1). This tells the spreadsheet to add exactly one month to the previous date.

Apply across the timeline

Click the small square at the bottom-right corner of cell E4 and drag the fill handle across the header row to apply the new monthly formula to the rest of the chart.

Format the date display

Select all the new date headers, right-click, and choose 'Format Cells'. Under the 'Number' tab, select 'Custom' and type 'mmm-yy' to display them neatly as Jan-24, Feb-24, etc.

Update Timeline Headers Using the EDATE Function

Handling End of Month: If you want the timeline to strictly display the last day of each month instead of the same numerical date, use =EOMONTH(D4, 0) for the current month's end, or =EOMONTH(D4, 1) for the next month.

Solution 2

Adjust Conditional Formatting for Monthly Spans

Modify the conditional formatting rules that render the Gantt chart bars to ensure they align properly with your newly updated monthly headers.

Seamless Project Management

Create and Manage Monthly Gantt Charts Easily in WPS Office

WPS Spreadsheet provides powerful data handling and flexible conditional formatting, making it incredibly easy to switch project timelines from weeks to months. With native support for advanced date functions, managing complex projects is smoother than ever.

1. Open your project file: Launch WPS Spreadsheet and open the workbook containing your weekly Gantt chart.
2. Apply the monthly formula: Select your second timeline header cell and input the formula =EDATE(reference_cell, 1).
3. Fill the timeline: Drag the fill handle to seamlessly extend the monthly intervals across your timeline.
4. Update chart visuals: Go to 'Home' > 'Conditional Formatting' to quickly adjust your bar rules to match the new dates.

Fully compatible with Microsoft Excel formulas like EDATE and EOMONTHRich built-in library of free, ready-to-use Gantt chart templatesIntuitive conditional formatting interface for precise visual project trackingLightweight and highly responsive, even with extensive timeline arrays

Frequently Asked Questions

Why did my Gantt chart bars disappear after changing to months?

This happens when your conditional formatting rules are still expecting 7-day increments, or if the formula doesn't correctly capture the start and end dates within a broader monthly column. You need to open Conditional Formatting > Manage Rules and verify the formula references your new monthly header row properly.

Can I use EOMONTH instead of EDATE for my timeline?

Yes. EOMONTH is excellent if you want your column headers to universally display the last day of the month (e.g., Jan 31, Feb 28). EDATE, on the other hand, adds exactly one month to the specific day of the starting date (e.g., Jan 15 becomes Feb 15).

How do I make the cells narrower for a monthly view?

To compress the chart visually, select all the columns representing your monthly timeline, right-click any of the selected column letters at the top, choose 'Column Width', and enter a smaller number.

Does WPS Office have pre-made monthly Gantt charts?

Yes, WPS Office offers a variety of free project management templates. You can open WPS, click 'New', search for 'Monthly Gantt Chart' in the template library, and immediately use a pre-formatted template without manually adjusting formulas.

Excel & Data Problems