+ Reply to Thread
Results 1 to 8 of 8

Display weeks and hours

  1. #1
    Registered User
    Join Date
    06-05-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    21

    Display weeks and hours

    Hello!
    I want to see on the chart weeks filled with hours.
    Attached example:
    See weeks: 1, 2, 3, 10, 12, 15, 19, 20, 21....

    Chart_example.xlsx
    Last edited by cascao; 06-20-2012 at 10:54 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Display weeks and hours

    sorry, im not understanding what you are trying to achieve? ould you explain in a bit more detail please?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-05-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Display weeks and hours

    I want to see on the chart weeks filled with hours.
    Attached example:
    Weeks: 1, 2, 3, 10, 12, 15, 19, 20, 21....

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,279

    Re: Display weeks and hours

    does this one work?
    Attached Files Attached Files
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    34

    Re: Display weeks and hours

    @ cascao ... i am not getting what exactly u need .. can u please explain a bit
    SANDEEP
    << press the star * if u like

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,827

    Re: Display weeks and hours

    Your current named range approach only determines the number of items to use but starts selecting that number from the top of the list. Which means the zero values as still included.

    You need to create an alternative set of data with the zero values removed.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  7. #7
    Registered User
    Join Date
    06-05-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Display weeks and hours

    Thank you all.
    The answer is what Andy said.

  8. #8
    Registered User
    Join Date
    06-05-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Display weeks and hours

    Andy, one more question:
    If the previous week, the hours are greater than 0. The next week, even if hours = 0, will appears in the chart..
    Example:
    WEEK 9 - 22:00:00 - show
    WEEK 10 - 0:00:00 - show
    WEEK 11 - 0:00:00 - hide
    WEEK 12 - 12:00:00 - show
    WEEK 13 - 18:00:00 - show
    ...

    Got it. I added OFFSET:
    =SMALL(IF($C$2:$C$22>0;$A$2:$A$22;IF(OFFSET($C$2:$C$22;-1;0)>0;$A$2:$A$22;ROWS($A$1:$A$22)));ROW($A$1:$A$21))
    Last edited by cascao; 06-21-2012 at 02:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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