Excel Gantt Chart View with week based timeline

Hi Experts,

I have few Activities/Tasks, with a start and end date. I am trying to show these activities in excel in a Gantt view so as to plan it better.
If I change the Start or End date of Activity, the view is updated dynamically.
In attached excel file, you will find two tabs - 'Day wise schedule' and 'Weekly Schedule'.
Day wise schedule is not a perfect view as it required too many columns and scrolling. So, I want to reduce this to weekly timeline.
My query is regarding Weekly Schedule tab where I am trying to transform 'Day Wise schedule' to 'Weekly Schedule'.
Instead of dates on timeline, I just wish to recreate this weekly timeline view.
Note that in the table we have date/weeks from Nov 2020 to Dec 2021.

Problem: In day wise schedule, I was comparing the time line date with Activity start and End date and then custom formatting to color the cell meaning activity is blocking that day.
However, in Weekly Schedule, I am not able to compare weeks when it is coming from different year e.g. week 49, 2020 need to be compared with week 10, 2021. But when I am comparing weeks the results will show 49>10.

I have done some work in Weekly schedule tab with weeks and year on timeline and also week start date and week end date is available on timeline.
But I am not able to create formula to compare timeline weeks with Activity start week and end week in combination respective year.
So, I just need one formula to compare timeline (week, year) with Activity start and end week range in cells starting from L5.


Regards,
Gaurav

If you use the WEEKNUM function, then this will be reset for each year, and as you have found this means that a start date in one year and an end date in another year will cause you problems.

It might be better to generate your own week numbers, and base this on consecutive numbers which are relative to the earliest start date, i.e. find the earliest start date and then subtract this from all other dates and divide the result by 7 to give the number of weeks from that original start date.

Hope this helps.

Pete

Hi Pete,

But this solution will not give me information on Calendar week. I would like to have calendar weeks in the chart.
Would it be possible to build a formula to compare weeks in combination of year ?

Hi,

Sorry I replied without trying your solution.
Now I got what you mean and I believe this will solve the issue.
You are genius !!

I've modified your file to reflect what I have in mind. The formula in L1 gives the Sunday of the week in which the earliest start date occurs (assuming this to be in E5), but you can just +1 at the end if you want the weekly dates to start on a Monday.

I've also changed the formulae in column H and Column J to give you the relatively week numbers.

Hope this helps.

Pete

P.S. Thanks for marking the thread as Solved - you might also like to click on the Add Reputation button to show your appreciation.