[EXCEL] Graphs with Dates
Solved
Guitou2804
-
Gvu -
Gvu -
Hello,
I am currently using Excel to create a chart representing the evolution of parameters over time. The data for this chart is gathered in a table, and each value corresponds to a recorded date. These dates therefore constitute the x-axis of my chart, but for better representation over time, I would like to see my dates incremented by one day on the axis. However, currently, when moving one unit on the x-axis of my chart, I can move several days as the parameter recordings are not made, for example, on weekends.
Of course, I could show the days where there are no recordings in my data table and leave the parameter fields empty, but I don't like that for the sake of readability.
Is there a solution to make Excel understand that we are dealing with dates and that I would like them to increment by 24 hours?
Thank you
Configuration: Windows XP / Internet Explorer 7.0
I am currently using Excel to create a chart representing the evolution of parameters over time. The data for this chart is gathered in a table, and each value corresponds to a recorded date. These dates therefore constitute the x-axis of my chart, but for better representation over time, I would like to see my dates incremented by one day on the axis. However, currently, when moving one unit on the x-axis of my chart, I can move several days as the parameter recordings are not made, for example, on weekends.
Of course, I could show the days where there are no recordings in my data table and leave the parameter fields empty, but I don't like that for the sake of readability.
Is there a solution to make Excel understand that we are dealing with dates and that I would like them to increment by 24 hours?
Thank you
Configuration: Windows XP / Internet Explorer 7.0
4 answers
Hello,
Once your chart is created, right-click on the x-axis and select "Format Axis"
In "Axis Options," choose the minimum and maximum dates that suit you best, then in the major unit, enter 1.
--
"Trotti Trotta, Mr. P, there are angels who want a kiss..."
"Even Mr. P's toys need toys."
Once your chart is created, right-click on the x-axis and select "Format Axis"
In "Axis Options," choose the minimum and maximum dates that suit you best, then in the major unit, enter 1.
--
"Trotti Trotta, Mr. P, there are angels who want a kiss..."
"Even Mr. P's toys need toys."
I will detail the procedure step by step that allows me to get what you want (procedure done on Excel 2007)
I take as an example a table of 2 columns over 5 rows. The first column, A, corresponds to the date formatted in this way (DD/MM/YYYY), the second, B, contains random values. The first row contains my table header.
I enter the following dates (01/01/2010, 05/01/2010, 07/01/2010, and 10/01/2010), I have deliberately chosen dates within an interval of only 15 days; beyond that, Excel may have more difficulty generating a chart with a single day unit on the x-axis.
I enter the values 4, 9, 7, and 10 respectively for each date.
I go to the Insert tab, then in the Charts area, and I click on Scatter, then on Scatter with Straight Lines and Markers.
I obtain a curve created by Excel's automatic settings.
I right-click on the x-axis values and select Format Axis.
In Axis Options, I leave everything on Automatic, except for Major Unit which I set to Fixed and put the value to 1.
In Alignment, I select Vertical text orientation to verify that it's indeed what I want.
I correctly obtain, on the x-axis, dates between 31/12/2009 and 11/01/2010, with a unit of one single day.
--
"Trotti Trotta, Mr. P, there are angels who want a kiss..."
"Even Mr. P's toys need toys"
I take as an example a table of 2 columns over 5 rows. The first column, A, corresponds to the date formatted in this way (DD/MM/YYYY), the second, B, contains random values. The first row contains my table header.
I enter the following dates (01/01/2010, 05/01/2010, 07/01/2010, and 10/01/2010), I have deliberately chosen dates within an interval of only 15 days; beyond that, Excel may have more difficulty generating a chart with a single day unit on the x-axis.
I enter the values 4, 9, 7, and 10 respectively for each date.
I go to the Insert tab, then in the Charts area, and I click on Scatter, then on Scatter with Straight Lines and Markers.
I obtain a curve created by Excel's automatic settings.
I right-click on the x-axis values and select Format Axis.
In Axis Options, I leave everything on Automatic, except for Major Unit which I set to Fixed and put the value to 1.
In Alignment, I select Vertical text orientation to verify that it's indeed what I want.
I correctly obtain, on the x-axis, dates between 31/12/2009 and 11/01/2010, with a unit of one single day.
--
"Trotti Trotta, Mr. P, there are angels who want a kiss..."
"Even Mr. P's toys need toys"
I confirm that by putting the graphic under a connected scatter plot, I do have a timeline with a one-day interval. However, it starts from 1/1/1900 even though I specify a column of dates in date format starting in May 2010... in fact, for this case, it assigns a number to each value starting from 1 up to n and converts this number into a date: 1 = 1/1/1900 obviously...
Perhaps I didn't specify "that my data was on a separate sheet (but personally I don't see the impact of that...)
Perhaps I didn't specify "that my data was on a separate sheet (but personally I don't see the impact of that...)
Please note that it doesn't work like that. Under 2007, I set the minimum interval to 1, I clearly specified that these are dates, but nothing at all; I always move from 06/18 to 06/21...
If I set the Date category, the series of numbers 1,2,3,4...n transforms into 1/1/1900, 2/1/1900, etc., which makes sense... What I don't understand is why it's not taking the values that I indicate on the x-axis... That is to say, my dates 25/5/10, 26/5/10, etc., entered in the correct "DATE" format in my data table...
Well, I just did this: I checked my data by selecting data. In the window that opened, I noticed that in the right "grayed out" column my dates appeared, and in the left column my data series appeared. I clicked (to see what it would give) on the button that allows swapping the abscissas with the ordinates, found myself with some random values on the x-axis, and my dates on the y-axis (logical...) then I clicked again and there was a miracle, I had my dates on the x-axis with an interval of 1 day...
I can't explain it... probably an Excel hiccup for generating the chronological axis from my graph already created in another format. Maybe if I had deleted and recreated my scatter plot right from the start instead of choosing the "modify chart" option, it would have generated what I wanted... Sorry though for my stubbornness and thank you for your advice and patience.
I can't explain it... probably an Excel hiccup for generating the chronological axis from my graph already created in another format. Maybe if I had deleted and recreated my scatter plot right from the start instead of choosing the "modify chart" option, it would have generated what I wanted... Sorry though for my stubbornness and thank you for your advice and patience.