Excel guide

How to make a weekly Gantt chart in Excel (one column per week)

A day per column gets too wide after a few months. With one column per week, a whole year fits on a screen. The trick is the bar rule: a week is coloured when the task overlaps any of its seven days, so a two-day task never falls between two columns. No chart object, no macros.

Want the finished file? Get the Gantt Chart Project Schedule on Gumroad ($7), or see screenshots first.

Updated 2026-11-16. Every formula block on this page was recalculated in a test workbook.

Why a week per column needs its own rule

The day-by-day Gantt guide colours a cell when the column's date lies between the task's start and end. Change the columns to one per week and that test only looks at one day of each week. A task that runs Tuesday to Thursday contains no Monday, so it gets no bar at all. The fix is to treat each column as a seven-day range and colour it when the task overlaps the range anywhere.

Step 1. The task list (same as the daily chart)

Put these headers in row 4: Task (A), Start (B), Days (C), End (D), Done % (E), Status (F), and enter tasks from row 5. Days counts the first day, so a 1-day task ends on its start date. In D5, then fill down:

D5=B5+C5-1

For working days only, use =WORKDAY(B5,C5-1) instead (start the task on a weekday). In A1 type Status date and in B1 =TODAY(), or a fixed reporting date. Status in F5, filled down:

F5=IF(E5>=1,"Done",IF(D5<$B$1,"Late",IF(B5>$B$1,"Planned","In progress")))

Step 2. Week columns that start on Monday

In A2 type View start. In B2, the Monday on or before the earliest task:

B2=MIN(B5:B20)-WEEKDAY(MIN(B5:B20),3)

WEEKDAY(date,3) numbers Monday 0 to Sunday 6, so subtracting it always lands on a Monday. If your weeks start on Sunday, use this in B2 instead (WEEKDAY with 1 numbers Sunday 1):

B2=MIN(B5:B20)-(WEEKDAY(MIN(B5:B20),1)-1)

You can also type any Monday (or Sunday) in B2 to scroll the chart.

Then the week columns. In G4 put =$B$2, in H4:

H4=G4+7

Copy H4 to the right: 26 columns are half a year, 52 a full year. Give row 4 the custom format d mmm (for example 2 Nov) and set columns G onward to a width of about 6.

Step 3. Week numbers

Many teams talk in week numbers. Put this in G3 and copy it right:

G3="W"&ISOWEEKNUM(G4)

ISOWEEKNUM gives the ISO 8601 week (weeks start Monday, week 1 contains the first Thursday of the year), which is what most planning tools and calendars in Europe use. Some years have a week 53, and the last days of December can belong to week 1 of the next year. ISOWEEKNUM needs Excel 2013 or later; Google Sheets and LibreOffice have it too. ISOWEEKNUM counts a Sunday as the last day of the previous week, so with the Sunday start from Step 2 the formula above would show the previous week's number. Use this in G3 instead; it numbers each column by its Monday:

G3="W"&ISOWEEKNUM(G4+1)

Step 4. Bars with an overlap rule

Select the grid starting at G5, for example G5:AF20, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. The whole bar (light blue):

=AND($B5<=G$4+6,$D5>=G$4)

Read it as: the task starts on or before the last day of this week (G$4+6) and ends on or after its first day (G$4). That is true for every week the task touches, even for one day, and for no other week.

The done part (dark blue) colours each week that contains at least one completed day. Completed days are counted from the start: Days x Done %, rounded.

=AND(ROUND($C5*$E5,0)>0,$B5<=G$4+6,$B5+ROUND($C5*$E5,0)-1>=G$4)

The first test stops a task at 0% (or at a share that rounds to 0 days) from colouring its first week. Put this rule above the whole-bar rule in Manage Rules so dark is drawn over light.

For the current week (light orange), add:

=AND($B$1>=G$4,$B$1<=G$4+6)

Late tasks work as in the daily chart: select A5:F20 and add a red rule:

=$F5="Late"
Gantt schedule with task list, date columns and blue bars (fictional sample data)
Our paid template in day view (fictional sample data). Its View unit cell switches the same grid to one column per 7 days.

Step 5. Read the weekly chart

  • A coloured week is not a full week of work. A task from Friday to the next Tuesday colours two weeks. The exact dates are in columns B and D.
  • Done is shown by the week. A 10-day task at 30% has 3 completed days. Every week that holds one of those days turns dark: usually just the first week, two weeks if the task starts on a Saturday or Sunday (a Friday or Saturday with Sunday-start weeks).
  • Freeze panes at G5 so the task list stays visible, and for printing choose Fit all columns on one page.
  • Want both views? Keep the daily sheet from the other guide for the next few weeks and this sheet for the whole project, both reading the same task list.

Common problems

  • Short tasks have no bar: the grid still uses the daily rule =AND(G$4>=$B5,G$4<=$D5). Replace it with the overlap rule from Step 4.
  • No bars at all, or bars in the wrong row: the rule was entered with a cell other than G5 active, or a $ is missing before B, C, D or E. Delete the rule, select from G5 and enter it again.
  • The first column is not a Monday (with Monday-start weeks): B2 uses WEEKDAY(...) without the 3, which counts Sunday as 1.
  • A 0% task shows a dark week: the done rule is missing ROUND($C5*$E5,0)>0.
  • Week numbers differ from your calendar app: the app uses US week numbers (WEEKNUM, weeks from 1 January) instead of ISO weeks.

Prefer a ready-made file?

This page gives you a working weekly Gantt chart. If you would rather start from a finished file, our paid Gantt Chart Project Schedule has 200 tasks with phase and owner columns, a View unit cell that switches the grid between one column per day and one column per 7 days, a skip-weekends option for end dates, and a summary of project start, end, overall progress and late tasks. The free lite edition does not include the week view.

Gantt Chart Project Schedule on Gumroad · USD 7

Need more than one template? This one is also in the Excel Templates Bundle (all 4) on Gumroad: $19 instead of $30 bought separately.

Screenshots and details of the template

Free Project Timeline & Working Days Calculator (runs in your browser)

More Excel guides

All how-to guides · More templates and free calculators

About this guide