Horizontal date axis incorrect on Excel line chart with secondary axis












3















My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect. Dates should range 1/1/2013 to 12/31/2013:



Date should range 1/1/2013 to 12/31/2013



All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.










share|improve this question





























    3















    My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect. Dates should range 1/1/2013 to 12/31/2013:



    Date should range 1/1/2013 to 12/31/2013



    All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.










    share|improve this question



























      3












      3








      3


      1






      My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect. Dates should range 1/1/2013 to 12/31/2013:



      Date should range 1/1/2013 to 12/31/2013



      All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.










      share|improve this question
















      My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect. Dates should range 1/1/2013 to 12/31/2013:



      Date should range 1/1/2013 to 12/31/2013



      All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.







      microsoft-excel charts






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 6 '17 at 2:05









      fixer1234

      19k144982




      19k144982










      asked Jan 5 '17 at 17:15









      jalea148jalea148

      21112




      21112






















          6 Answers
          6






          active

          oldest

          votes


















          1














          Revised; original answer wasn't accurate.



          Kevin's answer almost nailed it. Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart. The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.



          What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values. So your first date is input #1, the second is input #2, etc. Excel stores dates as a day count, starting with January 1, 1900 being day 1. When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900. Your last input is entry number 252, which corresponds with 9/8/1900.






          share|improve this answer


























          • No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

            – Jon Peltier
            Jan 12 '17 at 2:47











          • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

            – fixer1234
            Jan 12 '17 at 4:00



















          1














          I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.



          What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.



          For instance, plotting on axis:



          01.08.2015



          01.08.2015 00:30:00



          Won't work, but



          01.08.2015 00:30:00



          01.08.2015 00:34:00



          Should work.






          share|improve this answer
























          • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

            – mlibby
            Dec 13 '18 at 12:45



















          0














          It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:




          1
          2
          3
          4




          Then Excel will default to interpreting those as days since the beginning of "the era." You can cure this by entering the dates in full, like this:




          1/1/2013
          1/2/2013
          1/3/2013
          1/4/2013




          Additionally, it appears that your graph is ending in August (or maybe early September). If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.



          If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels. In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."






          share|improve this answer
























          • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

            – fixer1234
            Jan 5 '17 at 21:03











          • All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

            – jalea148
            Jan 5 '17 at 22:18











          • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

            – jalea148
            Jan 5 '17 at 23:00






          • 1





            What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

            – Jon Peltier
            Jan 12 '17 at 2:48



















          0














          Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked. This should help.



          One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.






          share|improve this answer































            0














            Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900. To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).






            share|improve this answer































              0














              For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell.
              My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared.
              My series definition was:
              =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)



              So I edited it to read this, and it fixed the problem
              =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)



              NOTE that all of my date cells were formatted as Date (as they should be) not Text.






              share|improve this answer























                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "3"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: true,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: 10,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1163837%2fhorizontal-date-axis-incorrect-on-excel-line-chart-with-secondary-axis%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                6 Answers
                6






                active

                oldest

                votes








                6 Answers
                6






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                1














                Revised; original answer wasn't accurate.



                Kevin's answer almost nailed it. Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart. The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.



                What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values. So your first date is input #1, the second is input #2, etc. Excel stores dates as a day count, starting with January 1, 1900 being day 1. When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900. Your last input is entry number 252, which corresponds with 9/8/1900.






                share|improve this answer


























                • No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

                  – Jon Peltier
                  Jan 12 '17 at 2:47











                • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

                  – fixer1234
                  Jan 12 '17 at 4:00
















                1














                Revised; original answer wasn't accurate.



                Kevin's answer almost nailed it. Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart. The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.



                What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values. So your first date is input #1, the second is input #2, etc. Excel stores dates as a day count, starting with January 1, 1900 being day 1. When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900. Your last input is entry number 252, which corresponds with 9/8/1900.






                share|improve this answer


























                • No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

                  – Jon Peltier
                  Jan 12 '17 at 2:47











                • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

                  – fixer1234
                  Jan 12 '17 at 4:00














                1












                1








                1







                Revised; original answer wasn't accurate.



                Kevin's answer almost nailed it. Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart. The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.



                What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values. So your first date is input #1, the second is input #2, etc. Excel stores dates as a day count, starting with January 1, 1900 being day 1. When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900. Your last input is entry number 252, which corresponds with 9/8/1900.






                share|improve this answer















                Revised; original answer wasn't accurate.



                Kevin's answer almost nailed it. Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart. The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.



                What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values. So your first date is input #1, the second is input #2, etc. Excel stores dates as a day count, starting with January 1, 1900 being day 1. When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900. Your last input is entry number 252, which corresponds with 9/8/1900.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Jan 12 '17 at 4:37

























                answered Jan 6 '17 at 2:02









                fixer1234fixer1234

                19k144982




                19k144982













                • No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

                  – Jon Peltier
                  Jan 12 '17 at 2:47











                • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

                  – fixer1234
                  Jan 12 '17 at 4:00



















                • No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

                  – Jon Peltier
                  Jan 12 '17 at 2:47











                • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

                  – fixer1234
                  Jan 12 '17 at 4:00

















                No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

                – Jon Peltier
                Jan 12 '17 at 2:47





                No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values.

                – Jon Peltier
                Jan 12 '17 at 2:47













                @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

                – fixer1234
                Jan 12 '17 at 4:00





                @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.

                – fixer1234
                Jan 12 '17 at 4:00













                1














                I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.



                What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.



                For instance, plotting on axis:



                01.08.2015



                01.08.2015 00:30:00



                Won't work, but



                01.08.2015 00:30:00



                01.08.2015 00:34:00



                Should work.






                share|improve this answer
























                • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

                  – mlibby
                  Dec 13 '18 at 12:45
















                1














                I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.



                What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.



                For instance, plotting on axis:



                01.08.2015



                01.08.2015 00:30:00



                Won't work, but



                01.08.2015 00:30:00



                01.08.2015 00:34:00



                Should work.






                share|improve this answer
























                • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

                  – mlibby
                  Dec 13 '18 at 12:45














                1












                1








                1







                I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.



                What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.



                For instance, plotting on axis:



                01.08.2015



                01.08.2015 00:30:00



                Won't work, but



                01.08.2015 00:30:00



                01.08.2015 00:34:00



                Should work.






                share|improve this answer













                I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.



                What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.



                For instance, plotting on axis:



                01.08.2015



                01.08.2015 00:30:00



                Won't work, but



                01.08.2015 00:30:00



                01.08.2015 00:34:00



                Should work.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Aug 15 '17 at 8:24









                sanraal1234sanraal1234

                111




                111













                • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

                  – mlibby
                  Dec 13 '18 at 12:45



















                • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

                  – mlibby
                  Dec 13 '18 at 12:45

















                Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

                – mlibby
                Dec 13 '18 at 12:45





                Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.

                – mlibby
                Dec 13 '18 at 12:45











                0














                It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:




                1
                2
                3
                4




                Then Excel will default to interpreting those as days since the beginning of "the era." You can cure this by entering the dates in full, like this:




                1/1/2013
                1/2/2013
                1/3/2013
                1/4/2013




                Additionally, it appears that your graph is ending in August (or maybe early September). If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.



                If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels. In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."






                share|improve this answer
























                • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

                  – fixer1234
                  Jan 5 '17 at 21:03











                • All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

                  – jalea148
                  Jan 5 '17 at 22:18











                • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

                  – jalea148
                  Jan 5 '17 at 23:00






                • 1





                  What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

                  – Jon Peltier
                  Jan 12 '17 at 2:48
















                0














                It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:




                1
                2
                3
                4




                Then Excel will default to interpreting those as days since the beginning of "the era." You can cure this by entering the dates in full, like this:




                1/1/2013
                1/2/2013
                1/3/2013
                1/4/2013




                Additionally, it appears that your graph is ending in August (or maybe early September). If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.



                If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels. In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."






                share|improve this answer
























                • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

                  – fixer1234
                  Jan 5 '17 at 21:03











                • All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

                  – jalea148
                  Jan 5 '17 at 22:18











                • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

                  – jalea148
                  Jan 5 '17 at 23:00






                • 1





                  What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

                  – Jon Peltier
                  Jan 12 '17 at 2:48














                0












                0








                0







                It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:




                1
                2
                3
                4




                Then Excel will default to interpreting those as days since the beginning of "the era." You can cure this by entering the dates in full, like this:




                1/1/2013
                1/2/2013
                1/3/2013
                1/4/2013




                Additionally, it appears that your graph is ending in August (or maybe early September). If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.



                If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels. In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."






                share|improve this answer













                It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:




                1
                2
                3
                4




                Then Excel will default to interpreting those as days since the beginning of "the era." You can cure this by entering the dates in full, like this:




                1/1/2013
                1/2/2013
                1/3/2013
                1/4/2013




                Additionally, it appears that your graph is ending in August (or maybe early September). If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.



                If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels. In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jan 5 '17 at 19:05









                Kevin TroyKevin Troy

                1596




                1596













                • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

                  – fixer1234
                  Jan 5 '17 at 21:03











                • All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

                  – jalea148
                  Jan 5 '17 at 22:18











                • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

                  – jalea148
                  Jan 5 '17 at 23:00






                • 1





                  What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

                  – Jon Peltier
                  Jan 12 '17 at 2:48



















                • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

                  – fixer1234
                  Jan 5 '17 at 21:03











                • All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

                  – jalea148
                  Jan 5 '17 at 22:18











                • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

                  – jalea148
                  Jan 5 '17 at 23:00






                • 1





                  What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

                  – Jon Peltier
                  Jan 12 '17 at 2:48

















                One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

                – fixer1234
                Jan 5 '17 at 21:03





                One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.

                – fixer1234
                Jan 5 '17 at 21:03













                All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

                – jalea148
                Jan 5 '17 at 22:18





                All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

                – jalea148
                Jan 5 '17 at 22:18













                The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

                – jalea148
                Jan 5 '17 at 23:00





                The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.

                – jalea148
                Jan 5 '17 at 23:00




                1




                1





                What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

                – Jon Peltier
                Jan 12 '17 at 2:48





                What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel.

                – Jon Peltier
                Jan 12 '17 at 2:48











                0














                Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked. This should help.



                One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.






                share|improve this answer




























                  0














                  Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked. This should help.



                  One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.






                  share|improve this answer


























                    0












                    0








                    0







                    Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked. This should help.



                    One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.






                    share|improve this answer













                    Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked. This should help.



                    One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Sep 22 '17 at 14:23









                    LbakerLbaker

                    1




                    1























                        0














                        Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900. To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).






                        share|improve this answer




























                          0














                          Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900. To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).






                          share|improve this answer


























                            0












                            0








                            0







                            Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900. To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).






                            share|improve this answer













                            Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900. To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Nov 17 '17 at 11:53









                            randomhelprandomhelp

                            1




                            1























                                0














                                For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell.
                                My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared.
                                My series definition was:
                                =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)



                                So I edited it to read this, and it fixed the problem
                                =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)



                                NOTE that all of my date cells were formatted as Date (as they should be) not Text.






                                share|improve this answer




























                                  0














                                  For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell.
                                  My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared.
                                  My series definition was:
                                  =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)



                                  So I edited it to read this, and it fixed the problem
                                  =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)



                                  NOTE that all of my date cells were formatted as Date (as they should be) not Text.






                                  share|improve this answer


























                                    0












                                    0








                                    0







                                    For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell.
                                    My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared.
                                    My series definition was:
                                    =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)



                                    So I edited it to read this, and it fixed the problem
                                    =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)



                                    NOTE that all of my date cells were formatted as Date (as they should be) not Text.






                                    share|improve this answer













                                    For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell.
                                    My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared.
                                    My series definition was:
                                    =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)



                                    So I edited it to read this, and it fixed the problem
                                    =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)



                                    NOTE that all of my date cells were formatted as Date (as they should be) not Text.







                                    share|improve this answer












                                    share|improve this answer



                                    share|improve this answer










                                    answered Jan 29 at 7:57









                                    Mike WalshMike Walsh

                                    111




                                    111






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Super User!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1163837%2fhorizontal-date-axis-incorrect-on-excel-line-chart-with-secondary-axis%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Plaza Victoria

                                        Puebla de Zaragoza

                                        Musa