Gantt Chart Formula

Hi,

Gantt chart for weekly viewing.
How to auto populate the calendar on top without key in one by one and easy to switch to another year?
How to auto populate red color column if actual end date excess than planned end date?
Please help.

Hi
To populate the week and easly switch to another year do the following:
In E2 put the Gantt start date you want.
In E3 use

Formula:

Please Login or Register  to view this content.

to get the efective start date (Monday)
Select J3:P3, merge that cells and use in J3 the formula

Formula:

Please Login or Register  to view this content.

Select J4:P4, merge that cells and use in J4

Formula:

Please Login or Register  to view this content.

Copy J4:P4 to Q4:BF4
Select Q3:W3, merge that cells and use in Q3 the formula

Formula:

Please Login or Register  to view this content.

Copy Q3:W3 toX3:BF3
I also apply conditional format to J3:BF3
select Q4:AD4 and fill to right to BF4
To get the chart use conditional format
Select J6:BF15
Apply conditional format using =$f6=J$2 in RED
Apply conditional format using =AND($E6<=J$2,$F6>=J$2) in PURPLE

See the file

Originally Posted by José Augusto

Hi
To populate the week and easly switch to another year do the following:
See the file

Thank you in advance.

But,

1. Is it possible the week is week 1 ,2 ,3 ,4 for particular month?
2. The red color to shown only if actual end date is greater than end date.
Exp: End date is 18.07.2016. Actual end date is 20.07.2016. Red color from 19 - 20 .07.2017.

Originally Posted by José Augusto

Hi
In J4 and copy forward
1. ="Week "&ISOWEEKNUM(J$2)-ISOWEEKNUM(DATE(YEAR(J$2),MONTH(J$2),1))

Select J6
Apply conditional format using (edit RED format formula)
2. =$G6=J$2

I am using excel 2007. Formula ISOWEEKNUM, is it not applicable to 2007?

Originally Posted by Shermaine2010

I am using excel 2007. Formula ISOWEEKNUM, is it not applicable to 2007?

No, but you can use WEEKNUM instead ISOWEEKNUM without problem.

Originally Posted by José Augusto

No, but you can use WEEKNUM instead ISOWEEKNUM without problem.

I got it, thanks.
But,

let's see example below the number of week doesn't accurate.

Example 1:
X2 = 27.06.2016. Q2 = 20.06.2016
="Week "&WEEKNUM(X$2)-WEEKNUM(Q$2) = Week 1. Correct should be week 4

Example 2:
AE2 = 04.07.2016 X2 = 27.06.2016
="Week "&WEEKNUM(AE$2)-WEEKNUM(DATE(YEAR(X2),MONTH(X2),1)) = Week 5 Correct should be week 1

How to correct it?

Hi
A date has the number of week corresponding to the number of weeks from the start of the year to that date. Thus 01/01/2016 corresponds to week 1 and day 01/03/2016 is already in week 2 to because weeks ending at Sunday.
If you want the week number relative to the beginning of the month you must use the difference to the first day of that month.
So the formula in example 1 is the number of the week the 27.06.2016 relatively to 20.06.2016 and is effectively 1
The formula in example 2 must be understanding as the number of the weeks between 01.06.2016 and 04.07.2017 and is effectively 5
If you want the number of the week you propose formulas are
="Week "&WEEKNUM(X$2)-WEEKNUM(DATE(YEAR(X2),MONTH(X2),1)) (Q2 is irrelevant and solution is 4)
="Week "&WEEKNUM(AE$2)-WEEKNUM(DATE(YEAR(AE2),MONTH(AE2),1)) (X2 is irrelevant and solution is 1)
The correct solution is
="Week "&WEEKNUM(AE$2)-WEEKNUM(DATE(YEAR(AE2),MONTH(AE2),1)+1
01-07-2017 --> 26 (Saturday - Week 1 of month 7), 02-07-2017 --> 26 (Sunday - Week 1 of month 7), 03-07-2017 --> 27 (Monday - Week 2 of month 7).
Regards

Originally Posted by José Augusto

Hi
A date has the number of week corresponding to the number of weeks from the start of the year to that date. Thus 01/01/2016 corresponds to week 1 and day 01/03/2016 is already in week 2 to because weeks ending at Sunday.
If you want the week number relative to the beginning of the month you must use the difference to the first day of that month.
So the formula in example 1 is the number of the week the 27.06.2016 relatively to 20.06.2016 and is effectively 1
The formula in example 2 must be understanding as the number of the weeks between 01.06.2016 and 04.07.2017 and is effectively 5
If you want the number of the week you propose formulas are
="Week "&WEEKNUM(X$2)-WEEKNUM(DATE(YEAR(X2),MONTH(X2),1)) (Q2 is irrelevant and solution is 4)
="Week "&WEEKNUM(AE$2)-WEEKNUM(DATE(YEAR(AE2),MONTH(AE2),1)) (X2 is irrelevant and solution is 1)
The correct solution is
="Week "&WEEKNUM(AE$2)-WEEKNUM(DATE(YEAR(AE2),MONTH(AE2),1)+1
01-07-2017 --> 26 (Saturday - Week 1 of month 7), 02-07-2017 --> 26 (Sunday - Week 1 of month 7), 03-07-2017 --> 27 (Monday - Week 2 of month 7).
Regards

Perhaps, make it simple. Take Monday as start to count the week. What the correct solution?

Example:
04.07.2016 - Week 1
25.07.2016 - Week 4
01.08.2016 - Week 1

Hi @Shermaine
Correct solution based on start week at Monday is (last formula I proposed)
04.07.2016 - Week 2
25.07.2016 - Week 5
01.08.2016 - Week 1