+ Reply to Thread
Results 1 to 5 of 5

not all months have 31 days

  1. #1
    jsc3489
    Guest

    not all months have 31 days

    I'm trying to create a "floating bar" chart that has the "# of Techs" as my
    catagory axis, months as my value axis. The chart displays the # of techs
    required to perform the job in each month (1 year). However, my months are
    all 31 days in my chart so my data gets a little skewed as the year goes by.

    I have created a formula to correct this, BUT only if the information in my
    "start date" column are consecutive, which they may not always be.

    My series data consists of the "start date" as series 1 and "# of days to
    complete" as series 2.

    Any way to "fool" the chart?


    -- I reject your reality and substitute my own.

    Promote hydrogen - one of the best "clean" fuels there are!

  2. #2
    Jon Peltier
    Guest

    Re: not all months have 31 days

    To clarify, could you post a sample of your data? Not a worksheet, just copy
    and paste in a reply. Indicate what you're using for X and for Y, and verify
    that you're using a "bar" chart and not a "column" chart.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    "jsc3489" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to create a "floating bar" chart that has the "# of Techs" as
    > my
    > catagory axis, months as my value axis. The chart displays the # of techs
    > required to perform the job in each month (1 year). However, my months are
    > all 31 days in my chart so my data gets a little skewed as the year goes
    > by.
    >
    > I have created a formula to correct this, BUT only if the information in
    > my
    > "start date" column are consecutive, which they may not always be.
    >
    > My series data consists of the "start date" as series 1 and "# of days to
    > complete" as series 2.
    >
    > Any way to "fool" the chart?
    >
    >
    > -- I reject your reality and substitute my own.
    >
    > Promote hydrogen - one of the best "clean" fuels there are!




  3. #3
    jsc3489
    Guest

    Re: not all months have 31 days

    Total Hours Start Date Techs. Required End Date Projected End
    Date Hours/Month Work Days to Complete Tech Hours Each Actual
    Days Techs./Month
    1000 1/3/2005 6 1/31/2005 168 20.83 166.67 28 5.95
    600 2/3/2005 4 3/1/2005 160 18.75 150.00 26 3.75
    450 3/1/2005 3 3/25/2005 184 18.75 150.00 24 2.45
    300 4/1/2005 2 4/27/2005 168 18.75 150.00 26 1.79
    1200 5/2/2005 7 5/31/2005 176 21.43 171.43 29 6.82
    500 6/1/2005 3 6/29/2005 176 20.83 166.67 28 2.84
    765 7/1/2005 5 7/28/2005 168 19.13 153.00 27 4.55
    275 8/1/2005 2 8/24/2005 184 17.19 137.50 23 1.49
    450 9/1/2005 3 9/27/2005 176 18.75 150.00 26 2.56
    230 10/3/2005 2 10/21/2005 168 14.38 115.00 18 1.37
    830 11/1/2005 5 11/29/2005 176 20.75 166.00 28 4.72
    1000 12/1/2005 6 12/29/2005 176 20.83 166.67 28 5.68


    Chart type "bar" or "floating bars"

    right now I'm trying your Arbitrary axis scale and I've
    downloaded the XY Chart Labeler and I'm not sure how to use.

    typically I would use "# of techs" as my Y and the "months" as my X

    --
    I reject your reality and substitute my own.

    Promote hydrogen - one of the best "clean" fuels there are!




  4. #4
    Jon Peltier
    Guest

    Re: not all months have 31 days

    You can use an area chart. I made a list of all dates, the first and last of
    each month, and put the Techs/Month value for the endpoints of each month:

    1/3/2005 5.95
    1/31/2005 5.95
    2/3/2005 3.75
    3/1/2005 3.75
    3/1/2005 2.45
    3/25/2005 2.45
    4/1/2005 1.79
    4/27/2005 1.79
    5/2/2005 6.82
    5/31/2005 6.82
    6/1/2005 2.84
    6/29/2005 2.84
    7/1/2005 4.55
    7/28/2005 4.55
    8/1/2005 1.49
    8/24/2005 1.49
    9/1/2005 2.56
    9/27/2005 2.56
    10/3/2005 1.37
    10/21/2005 1.37
    11/1/2005 4.72
    11/29/2005 4.72
    12/1/2005 5.68
    12/29/2005 5.68


    I plotted this in an Area chart. The X axis became a date axis. This
    represents the different lengths of each month well, but at the change of
    each month, you get a sloping line, where for example, from 1/31/05 to
    2/3/05 goes from 5.95 to 3.75

    To get vertical edges at the end of each period and a gap between for
    example 1/31/05 and 2/3/05, you need to insert more dates, actually
    duplicates of each date, sorted. Keep the Techs/month across each
    within-month span, and zeros for each inter-month span, and repeat the area
    chart. Here's the data:

    1/3/2005 0
    1/3/2005 5.95
    1/31/2005 5.95
    1/31/2005 0
    2/3/2005 0
    2/3/2005 3.75
    3/1/2005 3.75
    3/1/2005 0
    3/1/2005 0
    3/1/2005 2.45
    3/25/2005 2.45
    3/25/2005 0
    4/1/2005 0
    4/1/2005 1.79
    4/27/2005 1.79
    4/27/2005 0
    5/2/2005 0
    5/2/2005 6.82
    5/31/2005 6.82
    5/31/2005 0
    6/1/2005 0
    6/1/2005 2.84
    6/29/2005 2.84
    6/29/2005 0
    7/1/2005 0
    7/1/2005 4.55
    7/28/2005 4.55
    7/28/2005 0
    8/1/2005 0
    8/1/2005 1.49
    8/24/2005 1.49
    8/24/2005 0
    9/1/2005 0
    9/1/2005 2.56
    9/27/2005 2.56
    9/27/2005 0
    10/3/2005 0
    10/3/2005 1.37
    10/21/2005 1.37
    10/21/2005 0
    11/1/2005 0
    11/1/2005 4.72
    11/29/2005 4.72
    11/29/2005 0
    12/1/2005 0
    12/1/2005 5.68
    12/29/2005 5.68
    12/29/2005 0


    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    "jsc3489" <[email protected]> wrote in message
    news:[email protected]...
    > Total Hours Start Date Techs. Required End Date Projected End
    > Date Hours/Month Work Days to Complete Tech Hours Each Actual
    > Days Techs./Month
    > 1000 1/3/2005 6 1/31/2005 168 20.83 166.67 28 5.95
    > 600 2/3/2005 4 3/1/2005 160 18.75 150.00 26 3.75
    > 450 3/1/2005 3 3/25/2005 184 18.75 150.00 24 2.45
    > 300 4/1/2005 2 4/27/2005 168 18.75 150.00 26 1.79
    > 1200 5/2/2005 7 5/31/2005 176 21.43 171.43 29 6.82
    > 500 6/1/2005 3 6/29/2005 176 20.83 166.67 28 2.84
    > 765 7/1/2005 5 7/28/2005 168 19.13 153.00 27 4.55
    > 275 8/1/2005 2 8/24/2005 184 17.19 137.50 23 1.49
    > 450 9/1/2005 3 9/27/2005 176 18.75 150.00 26 2.56
    > 230 10/3/2005 2 10/21/2005 168 14.38 115.00 18 1.37
    > 830 11/1/2005 5 11/29/2005 176 20.75 166.00 28 4.72
    > 1000 12/1/2005 6 12/29/2005 176 20.83 166.67 28 5.68
    >
    >
    > Chart type "bar" or "floating bars"
    >
    > right now I'm trying your Arbitrary axis scale and I've
    > downloaded the XY Chart Labeler and I'm not sure how to use.
    >
    > typically I would use "# of techs" as my Y and the "months" as my X
    >
    > --
    > I reject your reality and substitute my own.
    >
    > Promote hydrogen - one of the best "clean" fuels there are!
    >
    >
    >




  5. #5
    jsc3489
    Guest

    Re: not all months have 31 days

    Thank you so much. Keen
    --
    I reject your reality and substitute my own.

    Promote hydrogen - one of the best "clean" fuels there are!


    "Jon Peltier" wrote:

    > You can use an area chart. I made a list of all dates, the first and last of
    > each month, and put the Techs/Month value for the endpoints of each month:
    >
    > 1/3/2005 5.95
    > 1/31/2005 5.95
    > 2/3/2005 3.75
    > 3/1/2005 3.75
    > 3/1/2005 2.45
    > 3/25/2005 2.45
    > 4/1/2005 1.79
    > 4/27/2005 1.79
    > 5/2/2005 6.82
    > 5/31/2005 6.82
    > 6/1/2005 2.84
    > 6/29/2005 2.84
    > 7/1/2005 4.55
    > 7/28/2005 4.55
    > 8/1/2005 1.49
    > 8/24/2005 1.49
    > 9/1/2005 2.56
    > 9/27/2005 2.56
    > 10/3/2005 1.37
    > 10/21/2005 1.37
    > 11/1/2005 4.72
    > 11/29/2005 4.72
    > 12/1/2005 5.68
    > 12/29/2005 5.68
    >
    >
    > I plotted this in an Area chart. The X axis became a date axis. This
    > represents the different lengths of each month well, but at the change of
    > each month, you get a sloping line, where for example, from 1/31/05 to
    > 2/3/05 goes from 5.95 to 3.75
    >
    > To get vertical edges at the end of each period and a gap between for
    > example 1/31/05 and 2/3/05, you need to insert more dates, actually
    > duplicates of each date, sorted. Keep the Techs/month across each
    > within-month span, and zeros for each inter-month span, and repeat the area
    > chart. Here's the data:
    >
    > 1/3/2005 0
    > 1/3/2005 5.95
    > 1/31/2005 5.95
    > 1/31/2005 0
    > 2/3/2005 0
    > 2/3/2005 3.75
    > 3/1/2005 3.75
    > 3/1/2005 0
    > 3/1/2005 0
    > 3/1/2005 2.45
    > 3/25/2005 2.45
    > 3/25/2005 0
    > 4/1/2005 0
    > 4/1/2005 1.79
    > 4/27/2005 1.79
    > 4/27/2005 0
    > 5/2/2005 0
    > 5/2/2005 6.82
    > 5/31/2005 6.82
    > 5/31/2005 0
    > 6/1/2005 0
    > 6/1/2005 2.84
    > 6/29/2005 2.84
    > 6/29/2005 0
    > 7/1/2005 0
    > 7/1/2005 4.55
    > 7/28/2005 4.55
    > 7/28/2005 0
    > 8/1/2005 0
    > 8/1/2005 1.49
    > 8/24/2005 1.49
    > 8/24/2005 0
    > 9/1/2005 0
    > 9/1/2005 2.56
    > 9/27/2005 2.56
    > 9/27/2005 0
    > 10/3/2005 0
    > 10/3/2005 1.37
    > 10/21/2005 1.37
    > 10/21/2005 0
    > 11/1/2005 0
    > 11/1/2005 4.72
    > 11/29/2005 4.72
    > 11/29/2005 0
    > 12/1/2005 0
    > 12/1/2005 5.68
    > 12/29/2005 5.68
    > 12/29/2005 0
    >
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    >
    > "jsc3489" <[email protected]> wrote in message
    > news:[email protected]...
    > > Total Hours Start Date Techs. Required End Date Projected End
    > > Date Hours/Month Work Days to Complete Tech Hours Each Actual
    > > Days Techs./Month
    > > 1000 1/3/2005 6 1/31/2005 168 20.83 166.67 28 5.95
    > > 600 2/3/2005 4 3/1/2005 160 18.75 150.00 26 3.75
    > > 450 3/1/2005 3 3/25/2005 184 18.75 150.00 24 2.45
    > > 300 4/1/2005 2 4/27/2005 168 18.75 150.00 26 1.79
    > > 1200 5/2/2005 7 5/31/2005 176 21.43 171.43 29 6.82
    > > 500 6/1/2005 3 6/29/2005 176 20.83 166.67 28 2.84
    > > 765 7/1/2005 5 7/28/2005 168 19.13 153.00 27 4.55
    > > 275 8/1/2005 2 8/24/2005 184 17.19 137.50 23 1.49
    > > 450 9/1/2005 3 9/27/2005 176 18.75 150.00 26 2.56
    > > 230 10/3/2005 2 10/21/2005 168 14.38 115.00 18 1.37
    > > 830 11/1/2005 5 11/29/2005 176 20.75 166.00 28 4.72
    > > 1000 12/1/2005 6 12/29/2005 176 20.83 166.67 28 5.68
    > >
    > >
    > > Chart type "bar" or "floating bars"
    > >
    > > right now I'm trying your Arbitrary axis scale and I've
    > > downloaded the XY Chart Labeler and I'm not sure how to use.
    > >
    > > typically I would use "# of techs" as my Y and the "months" as my X
    > >
    > > --
    > > I reject your reality and substitute my own.
    > >
    > > Promote hydrogen - one of the best "clean" fuels there are!
    > >
    > >
    > >

    >
    >
    >


+ 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