+ Reply to Thread
Results 1 to 16 of 16

Wrong dates in chart

  1. #1
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Wrong dates in chart

    Hello everybody,
    -Can you help me to solve my problem,please?
    -Wrong dates appear in the beginning of the axis and at the end
    -Question:
    1-Why wrong dates appear in the chart ?and how to fix this?
    2-How to show dates as it is(hide empty dates)?
    Find the attachment
    Attached Files Attached Files

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

    Re: Wrong dates in chart

    The horizontal axis has auto min/max enabled. Therefore the scale includes dates outside of those in your data.

    Change chart to line chart.
    Set axis position on tick marks
    Set line format of series to none
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Wrong dates in chart

    That's Excel's automatic scaling algorithm. Do you only want to remove the blank dates at the start and end, or also the blank dates within the actual data?
    Rory

  4. #4
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    I need to hide all blank dates

  5. #5
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    Quote Originally Posted by Andy Pope View Post
    The horizontal axis has auto min/max enabled. Therefore the scale includes dates outside of those in your data.

    Change chart to line chart.
    Set axis position on tick marks
    Set line format of series to none
    Thank you sir,great job,but how to hide all blank dates?

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Wrong dates in chart

    Don't use a date axis - set it to category. Then it will only plot actual data and not scale at all.

  7. #7
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    There are 3 axis types:
    -Automatically select based on data
    -text axis
    -Date axis
    How can I set it to category?

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Wrong dates in chart

    Choose Text.

  9. #9
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    When I choose text,It repeats dates as in data range and that is wrong
    1,1,1,3,3,3,4,4,4,4,4,5,5,5,5,5,..etc

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Wrong dates in chart

    Sorry, I hadn't looked closely enough to see that you have multiple points per date. I think you're out of luck (at least without a lot of work) unless Andy has a typically brilliant idea.

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

    Re: Wrong dates in chart

    It's possible to do with a couple of hacks.

    First create a set of data that has a series for each frequency of each date. Use formula to extract the required data value or -1.
    The use a pivot chart, line with markers, to plot all the frequencies.
    Format each series to appear the same.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    It`s difficult to create a pivot table for each supplier because I have 14 suppliers and I want to create 3 charts for every supplier
    14*3=42 charts

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

    Re: Wrong dates in chart

    Without working around the problem you will not get both multiple points per category AND exclusion on unused dates.

  14. #14
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    up.............................up

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

    Re: Wrong dates in chart

    what are you upping for?

    You either have charts that include the dates with no values in order to get multiple points on any date.
    Or you build complex series to plot only those dates with data.

    There is no simple solution to do both at the same time

  16. #16
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Wrong dates in chart

    Is there any solution without creating helping pivot tables? Using vba or anything?

+ 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. [SOLVED] SUMIF dates return wrong value
    By techsvc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-01-2018, 10:00 PM
  2. Replies: 4
    Last Post: 08-02-2017, 01:05 AM
  3. Wrong dates in database
    By LucasL1 in forum Excel General
    Replies: 1
    Last Post: 04-18-2014, 12:32 PM
  4. Dates Displayed Wrong Issue.
    By Dan86 in forum Excel General
    Replies: 7
    Last Post: 07-14-2013, 10:32 PM
  5. Dates are wrong, is there an update?
    By FrankMCook in forum Excel General
    Replies: 5
    Last Post: 03-06-2012, 09:38 AM
  6. dates automatically formatted wrong
    By bigajosepi in forum Excel General
    Replies: 1
    Last Post: 08-21-2006, 11:56 PM
  7. Replies: 2
    Last Post: 01-06-2005, 04:06 PM

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