graph with dates evenly spaced

I note that many of your "date" entries are actually text entries. The first step in getting a real date axis for your chart is to get all of your dates converted to real dates and not text (the DATEVALUE() function should be useful for this https://support.office.com/en-us/art...3-b7471bbff252 ). You may also want to spend some time with a discussion of how Excel stores dates and times as serial numbers: http://www.cpearson.com/Excel/datetime.htm#SerialDates

That may fix the axis issue by itself, as Excel will usually automatically display an axis based on dates as a date axis. In the event that Excel continues to treat the axis as a category/text axis, you can go into the format axis dialog and specify that this axis should be a date axis.

I hope that this will demonstrate what Mr. Shorty is saying.
First the values in column A are not all dates. Excel stores dates as the number of days since 1/1/1900. If the value in A2 was the date July 18, 2016 then changing the format from date to number would cause the cell to display 42569. Some of the values are dates, such as A11:A16.
One way to change the values to dates is to use the Text to Columns function on the Data tab. Select A2:A10; from the data tab run Text to Columns; Next; Next; select date; change drop down to DMY; Finish
You could continue this way for each contiguous range of text cells however you could also use the following formula, as shown in column C, to convert the text values to dates:

Formula:

Please Login or Register  to view this content.

You can then copy column C and paste it to column A as values only. An example of the outcome is shown on sheet2 of the attached file.
Let us know if you have any questions.

Note that number formatting does not change the underlying value of a cell. If you put =ISTEXT(A2) into D2, it will return TRUE. If you copy this formula down, it will indicate which entries are text and which are numbers.

As noted in the help file, the DATEVALUE() function uses your computer's date system setting to decide how to interpret dates.

Originally Posted by Excel help

Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example

I went into Window's Control Panel -> Regional and Language setting and specified dd-M-yyyy as the system default date setting, and the DATEVALUE() function worked just fine.

Even for those entries that are dates, did Excel correctly interpret them? I note that the first batch of text dates are in late July, but the first real date is 8 Feb (follow by 8 Mar, 8 Apr, etc.). Were these supposed to be August dates (2 Aug, 3 Aug, 4 Aug, etc)? If this is the case, I would go back to the data entry step and modify the data entry so that Excel will enter these dates correctly from the start, rather than trying to "fix" it after the fact. This will require knowing exactly where this data comes from and how it is getting entered into Excel, and then changing it until Excel will correctly interpret the entry.

If you must fix this after the fact, you might prefer to use the DATE() function instead, coupled with Excel's text manipulation functions (LEFT(), RIGHT(), MID()) to convert these text strings to dates. As an example, =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) Seems to work for the A2 -- though it will not work for A17, nor will it work for those that have been converted to the wrong date (if that is indeed the case). For A17, you could use =DATE(RIGHT(A17,4,MID(A17,1),LEFT(A17,2)). To convert those incorrect dates to the correct date, you could use the MONTH(), DAY() and YEAR() functions to get the day, month, and year that Excel erroneously entered for these, then put that into a DATE() function to get the correct dates. Something like =DATE(YEAR(A11),DAY(A11),MONTH(A11)) Will switch the day and month.

If you are unfamiliar with any of these function: https://support.office.com/en-us/art...1-63f26a86c0eb

I use exclusively scatter charts, so I am not as skilled with these line charts with date axis. If this were a scatter chart, it would be as simple as adding the second data set as a second data series (I usually use the select data dialog, as I find it a more robust method for selecting the desired X and Y value ranges). Then add the secondary Y axis (https://support.office.com/en-us/art...8-e1a2e14dd5a9 ).

On the rare occasions that I mess with line charts (mostly related to this forum), I find that I need both the secondary vertical and horizontal axes to get the desired effect (the help file above explains how to add both secondary axes to an existing chart). I also find that the only way to add the second data series with its own x-values data is to copy the range, then paste special -> as new series with x values in the first column. Note that you need to have the second series added to the chart before you can add the secondary axis system.

thanks MrShorty,

i followed the instructions but i wasn't able to add the secondary dataset. The problem is that my secondary dataset (21xx blue) is different to the first dataset , so it first need to
be adjusted, i guess, to match my existing graph... links to instructions or video wont help, i've already tried a few and they all take for granted that the data to add to an existing graph is formatted
in the same way already, too easy... could anyone help me directly on the spreadsheet?

this is the result when i tried to follow the instruction...
the problem is that the 2 value datasets ( 21xx blue and 43xx yellow ) are on a way different range.. so i need somehow to make them compatible, based
on the 43xx yellow values. if can someone help me directly on the file, it will be very appreciated,
many thanks!
Screenshot - 3_6_2017 , 2_52_49 AM.png

If I understand, you want both sets of data on the same graph, with the same horizontal axis and with separate vertical axes. I attempted to make a graph referencing the data while still on sheet 2, but for some reason the horizontal axis scale wouldn't work, so I ended up copying the data to sheet 1 as in the screenshot of post #9. After adding the second series to the graph I selected the second data series, chose format data series and selected secondary axis. Other than that I chose a solid line color for the second data series and matching font for the secondary axis.
Let us know if you have any questions.

Then, I took JeteMc's chart, selected the yellow "value2" data series, clicked on the formula bar in the "x_values" section of the SERIES() formula (=SERIES(series_name,Xvalues,Yvalues,series_order), and entered Sheet1!$C$2:$C$118 to get the second series to use column C for its X values.