Excel 2016 Chart showing random dates in x axis











up vote
1
down vote

favorite












I'm simply trying to graph some time-scale data using a date column and 3 data columns, but Excel keeps screwing up the dates in the chart... (showing dates from year 1900-1903 instead of 2018)



Screenshot



All of my dates in the date column are between the 12th and 13th of November, 2018 (12/11/2018 and 13/11/2018) as in the screenshot, and the entire date column number format is set to Custom: dd/mm/yyyy HH:mm (why doesn't Excel have this format as a predefined format??). I have also tried simply using the Date number format as well with no luck.



The Excel chart doesn't seem to think these are dates, since in the x-axis axis options, the bounds are numeric, not dates:



Screenshot



The date column is actually calculated from the Unix timestamp column and converted to my timezone (+10.5hrs) using the formula:



(((A2/60)/60)/24)+DATE(1970,1,1)+TIME(10,30,0)


Edit: I've also tried to copy the date column and paste as values. No luck.



Can someone tell me how to get the dates in the chart to actually use the dates in the date column?

And let me know why there might be random dates everywhere in the first place?










share|improve this question









New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • "to get the dates in the chart to actually use the dates in the date column?" <-- try to copy the (generated) date result.. and paste as values.. then do the formatting + chartGeneration. || IMHO, I think the root of this problem is excel 'see' the date columns as a formula.. not date..
    – p._phidot_
    9 hours ago












  • I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
    – Applez00800
    6 hours ago






  • 1




    It's treating the blank cells as zero. Dates are handled and stored as numbers (the number of days since January 0, 1900; that's why the chart is starting with 1900.
    – fixer1234
    3 hours ago















up vote
1
down vote

favorite












I'm simply trying to graph some time-scale data using a date column and 3 data columns, but Excel keeps screwing up the dates in the chart... (showing dates from year 1900-1903 instead of 2018)



Screenshot



All of my dates in the date column are between the 12th and 13th of November, 2018 (12/11/2018 and 13/11/2018) as in the screenshot, and the entire date column number format is set to Custom: dd/mm/yyyy HH:mm (why doesn't Excel have this format as a predefined format??). I have also tried simply using the Date number format as well with no luck.



The Excel chart doesn't seem to think these are dates, since in the x-axis axis options, the bounds are numeric, not dates:



Screenshot



The date column is actually calculated from the Unix timestamp column and converted to my timezone (+10.5hrs) using the formula:



(((A2/60)/60)/24)+DATE(1970,1,1)+TIME(10,30,0)


Edit: I've also tried to copy the date column and paste as values. No luck.



Can someone tell me how to get the dates in the chart to actually use the dates in the date column?

And let me know why there might be random dates everywhere in the first place?










share|improve this question









New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • "to get the dates in the chart to actually use the dates in the date column?" <-- try to copy the (generated) date result.. and paste as values.. then do the formatting + chartGeneration. || IMHO, I think the root of this problem is excel 'see' the date columns as a formula.. not date..
    – p._phidot_
    9 hours ago












  • I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
    – Applez00800
    6 hours ago






  • 1




    It's treating the blank cells as zero. Dates are handled and stored as numbers (the number of days since January 0, 1900; that's why the chart is starting with 1900.
    – fixer1234
    3 hours ago













up vote
1
down vote

favorite









up vote
1
down vote

favorite











I'm simply trying to graph some time-scale data using a date column and 3 data columns, but Excel keeps screwing up the dates in the chart... (showing dates from year 1900-1903 instead of 2018)



Screenshot



All of my dates in the date column are between the 12th and 13th of November, 2018 (12/11/2018 and 13/11/2018) as in the screenshot, and the entire date column number format is set to Custom: dd/mm/yyyy HH:mm (why doesn't Excel have this format as a predefined format??). I have also tried simply using the Date number format as well with no luck.



The Excel chart doesn't seem to think these are dates, since in the x-axis axis options, the bounds are numeric, not dates:



Screenshot



The date column is actually calculated from the Unix timestamp column and converted to my timezone (+10.5hrs) using the formula:



(((A2/60)/60)/24)+DATE(1970,1,1)+TIME(10,30,0)


Edit: I've also tried to copy the date column and paste as values. No luck.



Can someone tell me how to get the dates in the chart to actually use the dates in the date column?

And let me know why there might be random dates everywhere in the first place?










share|improve this question









New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











I'm simply trying to graph some time-scale data using a date column and 3 data columns, but Excel keeps screwing up the dates in the chart... (showing dates from year 1900-1903 instead of 2018)



Screenshot



All of my dates in the date column are between the 12th and 13th of November, 2018 (12/11/2018 and 13/11/2018) as in the screenshot, and the entire date column number format is set to Custom: dd/mm/yyyy HH:mm (why doesn't Excel have this format as a predefined format??). I have also tried simply using the Date number format as well with no luck.



The Excel chart doesn't seem to think these are dates, since in the x-axis axis options, the bounds are numeric, not dates:



Screenshot



The date column is actually calculated from the Unix timestamp column and converted to my timezone (+10.5hrs) using the formula:



(((A2/60)/60)/24)+DATE(1970,1,1)+TIME(10,30,0)


Edit: I've also tried to copy the date column and paste as values. No luck.



Can someone tell me how to get the dates in the chart to actually use the dates in the date column?

And let me know why there might be random dates everywhere in the first place?







microsoft-excel charts microsoft-excel-2016






share|improve this question









New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 6 hours ago





















New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 14 hours ago









Applez00800

164




164




New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












  • "to get the dates in the chart to actually use the dates in the date column?" <-- try to copy the (generated) date result.. and paste as values.. then do the formatting + chartGeneration. || IMHO, I think the root of this problem is excel 'see' the date columns as a formula.. not date..
    – p._phidot_
    9 hours ago












  • I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
    – Applez00800
    6 hours ago






  • 1




    It's treating the blank cells as zero. Dates are handled and stored as numbers (the number of days since January 0, 1900; that's why the chart is starting with 1900.
    – fixer1234
    3 hours ago


















  • "to get the dates in the chart to actually use the dates in the date column?" <-- try to copy the (generated) date result.. and paste as values.. then do the formatting + chartGeneration. || IMHO, I think the root of this problem is excel 'see' the date columns as a formula.. not date..
    – p._phidot_
    9 hours ago












  • I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
    – Applez00800
    6 hours ago






  • 1




    It's treating the blank cells as zero. Dates are handled and stored as numbers (the number of days since January 0, 1900; that's why the chart is starting with 1900.
    – fixer1234
    3 hours ago
















"to get the dates in the chart to actually use the dates in the date column?" <-- try to copy the (generated) date result.. and paste as values.. then do the formatting + chartGeneration. || IMHO, I think the root of this problem is excel 'see' the date columns as a formula.. not date..
– p._phidot_
9 hours ago






"to get the dates in the chart to actually use the dates in the date column?" <-- try to copy the (generated) date result.. and paste as values.. then do the formatting + chartGeneration. || IMHO, I think the root of this problem is excel 'see' the date columns as a formula.. not date..
– p._phidot_
9 hours ago














I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
6 hours ago




I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
6 hours ago




1




1




It's treating the blank cells as zero. Dates are handled and stored as numbers (the number of days since January 0, 1900; that's why the chart is starting with 1900.
– fixer1234
3 hours ago




It's treating the blank cells as zero. Dates are handled and stored as numbers (the number of days since January 0, 1900; that's why the chart is starting with 1900.
– fixer1234
3 hours ago










1 Answer
1






active

oldest

votes

















up vote
1
down vote













I found the issue, it was due to me extending the chart source data beyond the rows that had values in them. I wanted to extend it a couple hundred blank rows below so that I could simply paste a new table of values each day and not have to re-source the chart data. Turns out you can't do this...



I selected just the rows with data in them and now it's displaying correctly.






share|improve this answer








New contributor




Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


















    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',
    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
    });


    }
    });






    Applez00800 is a new contributor. Be nice, and check out our Code of Conduct.










     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1375167%2fexcel-2016-chart-showing-random-dates-in-x-axis%23new-answer', 'question_page');
    }
    );

    Post as a guest
































    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    1
    down vote













    I found the issue, it was due to me extending the chart source data beyond the rows that had values in them. I wanted to extend it a couple hundred blank rows below so that I could simply paste a new table of values each day and not have to re-source the chart data. Turns out you can't do this...



    I selected just the rows with data in them and now it's displaying correctly.






    share|improve this answer








    New contributor




    Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      1
      down vote













      I found the issue, it was due to me extending the chart source data beyond the rows that had values in them. I wanted to extend it a couple hundred blank rows below so that I could simply paste a new table of values each day and not have to re-source the chart data. Turns out you can't do this...



      I selected just the rows with data in them and now it's displaying correctly.






      share|improve this answer








      New contributor




      Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.




















        up vote
        1
        down vote










        up vote
        1
        down vote









        I found the issue, it was due to me extending the chart source data beyond the rows that had values in them. I wanted to extend it a couple hundred blank rows below so that I could simply paste a new table of values each day and not have to re-source the chart data. Turns out you can't do this...



        I selected just the rows with data in them and now it's displaying correctly.






        share|improve this answer








        New contributor




        Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        I found the issue, it was due to me extending the chart source data beyond the rows that had values in them. I wanted to extend it a couple hundred blank rows below so that I could simply paste a new table of values each day and not have to re-source the chart data. Turns out you can't do this...



        I selected just the rows with data in them and now it's displaying correctly.







        share|improve this answer








        New contributor




        Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        share|improve this answer



        share|improve this answer






        New contributor




        Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        answered 6 hours ago









        Applez00800

        164




        164




        New contributor




        Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.





        New contributor





        Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






        Applez00800 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






















            Applez00800 is a new contributor. Be nice, and check out our Code of Conduct.










             

            draft saved


            draft discarded


















            Applez00800 is a new contributor. Be nice, and check out our Code of Conduct.













            Applez00800 is a new contributor. Be nice, and check out our Code of Conduct.












            Applez00800 is a new contributor. Be nice, and check out our Code of Conduct.















             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1375167%2fexcel-2016-chart-showing-random-dates-in-x-axis%23new-answer', 'question_page');
            }
            );

            Post as a guest




















































































            Popular posts from this blog

            Plaza Victoria

            Puebla de Zaragoza

            Musa