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)
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:
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
asked Nov 13 at 23:09
Applez00800
165
add a comment |
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)
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:
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
asked Nov 13 at 23:09
Applez00800
165
"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_
Nov 14 at 3:49
I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
Nov 14 at 7:03
2
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
Nov 14 at 9:45
This is true, but my dates are all greater than 43400 numeric.. so the plot should have shown 1900 (0) all the way to the last date in the list (43417.87397) and all of my data should have then been bunched up at the far right, but it's not.. which is why I was confused
– Applez00800
Nov 14 at 21:25
add a comment |
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)
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:
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
asked Nov 13 at 23:09
Applez00800
165
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)
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:
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
microsoft-excel charts microsoft-excel-2016
asked Nov 13 at 23:09
Applez00800
165
asked Nov 13 at 23:09
Applez00800
165
edited Nov 14 at 7:05
asked Nov 13 at 23:09
Applez00800
165
asked Nov 13 at 23:09
Applez00800
165
asked Nov 13 at 23:09
Applez00800
165
165
"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_
Nov 14 at 3:49
I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
Nov 14 at 7:03
2
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
Nov 14 at 9:45
This is true, but my dates are all greater than 43400 numeric.. so the plot should have shown 1900 (0) all the way to the last date in the list (43417.87397) and all of my data should have then been bunched up at the far right, but it's not.. which is why I was confused
– Applez00800
Nov 14 at 21:25
add a comment |
"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_
Nov 14 at 3:49
I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
Nov 14 at 7:03
2
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
Nov 14 at 9:45
This is true, but my dates are all greater than 43400 numeric.. so the plot should have shown 1900 (0) all the way to the last date in the list (43417.87397) and all of my data should have then been bunched up at the far right, but it's not.. which is why I was confused
– Applez00800
Nov 14 at 21:25
"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_
Nov 14 at 3:49
"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_
Nov 14 at 3:49
I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
Nov 14 at 7:03
I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
Nov 14 at 7:03
2
2
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
Nov 14 at 9:45
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
Nov 14 at 9:45
This is true, but my dates are all greater than 43400 numeric.. so the plot should have shown 1900 (0) all the way to the last date in the list (43417.87397) and all of my data should have then been bunched up at the far right, but it's not.. which is why I was confused
– Applez00800
Nov 14 at 21:25
This is true, but my dates are all greater than 43400 numeric.. so the plot should have shown 1900 (0) all the way to the last date in the list (43417.87397) and all of my data should have then been bunched up at the far right, but it's not.. which is why I was confused
– Applez00800
Nov 14 at 21:25
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Nov 14 at 7:16
Applez00800
165
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.
1
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
1
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Nov 14 at 7:16
Applez00800
165
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.
1
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
1
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
add a comment |
up vote
1
down vote
accepted
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.
answered Nov 14 at 7:16
Applez00800
165
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.
1
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
1
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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.
answered Nov 14 at 7:16
Applez00800
165
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.
answered Nov 14 at 7:16
Applez00800
165
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 Nov 14 at 7:16
Applez00800
165
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 Nov 14 at 7:16
Applez00800
165
answered Nov 14 at 7:16
Applez00800
165
165
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.
1
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
1
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
add a comment |
1
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
1
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
1
1
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
If you put your data into an Excel table format you should be able to add rows and the new data reflect in your chart.
– Rey Juna
Nov 14 at 22:42
1
1
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
you may accept (press the tick) on your own answers to mark this question as "answered". ( :
– p._phidot_
Nov 16 at 19:43
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
Whoops, thought I marked it as the answer already, cheers for the bump
– Applez00800
Nov 17 at 4:56
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
"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_
Nov 14 at 3:49
I've tried this as well without any luck either, forgot to mention in my question. I'll add it in. cheers
– Applez00800
Nov 14 at 7:03
2
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
Nov 14 at 9:45
This is true, but my dates are all greater than 43400 numeric.. so the plot should have shown 1900 (0) all the way to the last date in the list (43417.87397) and all of my data should have then been bunched up at the far right, but it's not.. which is why I was confused
– Applez00800
Nov 14 at 21:25