Chart & Visualization Issues

How to Change an Excel Gantt Chart from Weekly to Monthly

Maira Mehtab Sep 27, 2026 868 views

Question details

The user needs to change the time intervals on an existing Excel Gantt chart from a weekly display to a monthly display.

Product: Excel
Device & OS: not provided
Scenario: Adjusting the timeline view of a spreadsheet-based project management Gantt chart to show a broader monthly overview instead of weekly increments.
Observed behavior: The current Gantt chart timeline increments by weeks, and standard built-in view or timeline formatting options are unavailable or insufficient to convert it to a monthly view.

Before you start

Before modifying the chart, identify the specific cell containing your project's start date and note the range of cells where your current weekly timeline headers are located.

Solution 1Recommended

Use the EDATE Formula to Modify Timeline Intervals

Replace the existing weekly addition formulas (like +7) in your timeline header with the EDATE function to dynamically generate monthly intervals.

Spreadsheet-based Gantt charts typically rely on a sequence of formulas across the top row to determine the timeline. By substituting standard day-addition formulas with a function that calculates exact months, the entire chart will recalibrate to a monthly view.

Locate the Timeline Header

Click on the second date cell in your Gantt chart's timeline header row (the cell immediately following your initial project start date).

Apply the EDATE Formula

Replace the existing weekly formula with =EDATE(A1, 1), ensuring you replace 'A1' with the cell reference of your initial project start date. This adds exactly one month to the previous date.

Extend the Formula

Click the small square at the bottom right corner of the cell containing your new EDATE formula, and drag the fill handle horizontally across the rest of the timeline row to apply the monthly increments.

Adjust Column Widths

Select all the columns encompassing your new monthly timeline. Right-click the column headers and select 'Column Width', then input a smaller value to condense the view and properly display the new monthly Gantt structure.

Formatting the EDATE Output: If the EDATE formula returns a 5-digit number instead of a date (e.g., 44500), select the timeline cells, press Ctrl+1 to open Format Cells, and apply your preferred Date format.

Create and Manage Gantt Charts Easily in WPS Spreadsheet

WPS Spreadsheet provides robust formula support, including EDATE, and seamless conditional formatting to help you build and customize project Gantt charts effortlessly.

1. Open your File in WPS Spreadsheet: Launch WPS Office and open your existing Gantt chart workbook.
2. Select the Timeline Cells: Navigate to the header row where your weekly dates are displayed and click the second date cell in the sequence.
3. Input the Monthly Formula: Type =EDATE(Previous_Date_Cell, 1) and press Enter to calculate the exact same date for the following month.
4. Drag to Fill: Use the fill handle on the bottom right of the active cell and drag it across the row to populate the remaining monthly headers.
5. Optimize Layout: Highlight the timeline columns, go to the Home tab, click 'Format', and choose 'AutoFit Column Width' to organize the visual spacing.

100% compatible with Microsoft Excel (.xlsx) formulas and formattingSupports advanced date functions like EDATE and EOMONTH nativelyRich conditional formatting engine for precise visual task trackingFree, lightweight, and user-friendly interface for project management

microsoft office alternative - wps office

Frequently Asked Questions

Can I use EOMONTH instead of EDATE for my Gantt timeline?

Yes. While EDATE keeps the same day of the month (e.g., Jan 15 to Feb 15), EOMONTH will return the last day of the target month (e.g., Jan 31 to Feb 28). Use =EOMONTH(Start_Date, 0) for the current month's end, or 1 for the next month's end, depending on how you want to align your tasks.

Why did my conditional formatting bars disappear after changing to a monthly view?

Conditional formatting rules in Gantt charts compare task start and end dates against the timeline header. If you change the header from weekly to monthly, tasks that do not span across the exact monthly header dates might not trigger the formula correctly. You may need to adjust your conditional formatting formula to check if a task falls within the month rather than on specific days.

How do I change the timeline to show only the Month and Year text?

Select the timeline header cells containing your dates. Right-click, select 'Format Cells', go to the 'Custom' category, and type 'mmm-yy' (without quotes) into the Type box. This will display dates like 'Jan-23' while retaining the underlying exact date value needed for Gantt formulas.

Is there a way to have both a weekly and monthly view in the same chart?

Yes, you can create a two-tier timeline. Insert a new row above your weekly dates. In the new top row, use formulas or manual entries to label the spanning months, and use 'Merge & Center' across the corresponding weekly columns below it to create a grouped visualization.

Excel & Data Problems