+ Reply to Thread
Results 1 to 10 of 10

Reworking Gannt-chart to show years and months

  1. #1
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Reworking Gannt-chart to show years and months

    I can't figure out how to change my Gannt chart to display years and all 12 months in the year. The attached picture is how I imagine it (the timelines are not correct in the example though, as I just manually altered the year/month)

    The excel-file is what I got.

    Instead of displaying the year as it is now (with 8 boxes all displaying the same year, 2021), I want two boxes displaying 2021 and 2022 (if that adds up for the dates for the tasks). Below that I want each month for the year and then below that, the timeline for the different tasks (as it is now, so no tweaking here)

    I still want to maintain the "Display month" function, which is supposed to fast foward the gannt chart, according to which month number you choose to input. Say I input "3" in the field. The gannt chart will start at "march" as it is the 3rd month. Any help is appreciated! Thank you very much!
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Allerdrengen; 09-22-2021 at 11:43 AM.

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Reworking Gannt-chart to show years and months

    here is a version that uses data validation of a list of range types (daily, weekly, monthly, etc).

    it differs from the vertex42 one you are using by having the date in a single row, however with some careful editing and custom formatting it shouldnt be too difficult to make with a style similar you have now, or to the image.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: Reworking Gannt-chart to show years and months

    Hi, thanks for your response and help. While it is definitely closer to what I'm trying to achieve, I have a hard time getting it to where I want. I attached a new excel ark with a more accurate representation of what it is that I'm looking for. The years and numbers are manually inputted while the timeline doesn't add up, but its just to show how I'm imagining it.
    Attached Files Attached Files
    Last edited by Allerdrengen; 09-22-2021 at 09:42 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Reworking Gannt-chart to show years and months

    You will need to have a table with initial start and final end for each task. you could then use the formulae in the Gannt chart posted by JanMorris to generate a monthly view.

    If you want a year heading i.e 12 columns wide, you will need VBA to select the columns and merge the cells.

    Equal VBA will be need to unmerge for the daily view. (drop down selection foe view)

    Or just simply have another sheet with the year view so no need for VBA.

  5. #5
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: Reworking Gannt-chart to show years and months

    Thanks for your response! As you can see from my sheet, I already have a table with start and end. What formula specificly are you referring to?
    As I can see from JanMorris, its not quite like my desired example in terms of months.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Reworking Gannt-chart to show years and months

    In "Gannt with Dynamic view" look at formula in row 4 (H4 onwards) which uses the G2 drop down to determine the dates in row 4 and hence the Conditional Formatting..

    For the monthly it does what you want: you can change the "cosmetics" i.e headings to "mmm" or whatever.

    Your Gannt table has multiple entries for a given task so if want an "overview" of a given task i.e one line , you need to extract the INITIAL start date and FINAL end date for each task (or sub-task).

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Reworking Gannt-chart to show years and months

    Is this what is required.
    For date selection in H5

    =AGGREGATE(15,6,$D$8:$D$36,1)-1

    Select H8:BK38. Formula for CF

    =AND(H$5>=$D8,H$5<=$E8)
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 09-22-2021 at 10:58 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: Reworking Gannt-chart to show years and months

    Hi. I believe that is not exactly what is required. In the header rows, I need the year eg. 2021 and then all 12 months for that year. Not the actual dates.

  9. #9
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Reworking Gannt-chart to show years and months

    then you will need to use VBA to force the change of the format type every time you want it to change from a date to a month, and for the month to the year on the other row.

    an alternative would be to duplicate the sheet, have one with date view, and another with month view.
    Last edited by janmorris; 09-22-2021 at 07:56 PM.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Reworking Gannt-chart to show years and months

    May be this. Pl see file.
    Formula for CF

    =AND(H$5>=$D8,H$5<=$E8,$D8<>"",$E8<>"")
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 01-27-2020, 12:46 PM
  2. [SOLVED] DATEDIF to Show "Years-Months" Results
    By Mvaldesi in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 06-28-2018, 03:58 PM
  3. [SOLVED] Ho do I change the formula for this calendar to show months and years?
    By john dalton in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-01-2014, 10:39 AM
  4. Replies: 1
    Last Post: 10-04-2013, 02:58 PM
  5. [SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.
    By sharpmel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-17-2013, 04:20 PM
  6. [SOLVED] Chart comparing months between 2 years
    By Debra in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-11-2006, 06:15 PM
  7. Show quarters and years on x axis of a chart
    By Kacee in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-19-2005, 12:06 AM

Tags for this Thread

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