+ Reply to Thread
Results 1 to 10 of 10

Gantt Chart Formula

  1. #1
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    212

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Gantt Chart Formula

    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.

    Select J4:P4, merge that cells and use in J4
    Formula: copy to clipboard
    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: copy to clipboard
    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
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    212

    Re: Gantt Chart Formula

    Quote Originally Posted by Josť Augusto View Post
    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.

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Gantt Chart Formula

    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
    Attached Files Attached Files
    Last edited by Josť Augusto; 07-18-2016 at 10:08 AM.

  5. #5
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    212

    Re: Gantt Chart Formula

    Quote Originally Posted by Josť Augusto View Post
    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?

  6. #6
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Gantt Chart Formula

    Quote Originally Posted by Shermaine2010 View Post
    I am using excel 2007. Formula ISOWEEKNUM, is it not applicable to 2007?
    No, but you can use WEEKNUM instead ISOWEEKNUM without problem.

  7. #7
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    212

    Re: Gantt Chart Formula

    Quote Originally Posted by Josť Augusto View Post
    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?

  8. #8
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Gantt Chart Formula

    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

  9. #9
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    212

    Re: Gantt Chart Formula

    Quote Originally Posted by Josť Augusto View Post
    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

  10. #10
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Gantt Chart Formula

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Date Formula - gantt chart view
    By kwill1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2014, 05:20 PM
  2. [SOLVED] Formula for dynamic excel gantt chart
    By Ex-xcel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-02-2013, 11:30 AM
  3. Replies: 5
    Last Post: 10-04-2012, 07:01 AM
  4. Replies: 0
    Last Post: 07-13-2012, 06:40 PM
  5. [SOLVED] Formula to display Gantt Chart
    By stait in forum Outlook Formatting & Functions
    Replies: 3
    Last Post: 04-19-2012, 07:12 PM
  6. Gantt Chart Formula
    By Jon Excel in forum Excel General
    Replies: 1
    Last Post: 04-25-2011, 01:44 PM
  7. formula for gantt chart
    By sanlen in forum Excel General
    Replies: 1
    Last Post: 08-31-2010, 08:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1