How do I get a chart to work with irregular dates correctly?

[NotSparta]

Hello,

I've tried for some time now to create a chart where the x-axis make up for irregular dates. Making the line between 2 points longer if it's long time between them and short if it's short time between them.

I've finally understood line charts doesn't support that and I have to use scatter graph.
It seems to work but the dates, the get all messed up. I type 2014-11-11 but the x-axis in the graph shows 2283-12-12 or something like that.

I've googled and googled but can't see to find a solution. Got a notion that I might have to transform it to the date value manually first but I get #VALUE ERROR (roughly translated, not english office unfortunately). I.e. 2014-11-11 in A1, then I type in A2 =DATEVALUE(A1).

So main question is how do I get a chart to work with irregular dates correctly?

Thank you.

 

[pplstuff]

One approach would be to manipulate the chart series... This is kind of abstract to explain in a short post, but hopefully you can follow the idea...

Make the x-axis have 100 Data points... Then use a formula to index the dates/data you want plotted... The formula should identify the "spacing" required between each date....
For example:
If you want to plot January, Febuary, November, and December... the formula should identify that there are 8 months between and should leav the appropriate number of "blanks" in the 100 point range (I say 100 data points, but you can use any number - hopefully you see the idea to 'trick' the chart.

You may be able to connect Feb to Nov with the line, or you may have to play with the numbers to "create" a line between Feb and Nov.

Hopefully, you see the concept and it make sense to create these 'dummy' series in the chart.

 

[NotSparta]

Thanks!

You mean I use number 1 to 365 instead of dates for example?
Any idea how a formula like that would look like, so I still can have the dates typed out (and not just hard-to-translate numbers) in both the document and on x-axis of the chart? So the data point(s) is just a silent middleman so to speak.

Are there other ways? In my head it sounds like it should be more easy, especially since Excel have support for time and dates. Hm.

 

[pplstuff]

There are probably easier ways to do it, this was just the first thing that popped in my head... Using a chart range of 365 would make it simpler to explain, lol.

1/1/13 --- 5
2/1/13 --- 10
11/1/13 --- 15
12/1/13 --- 20

Then for the the range of 365 cells, I'd have 3 columns
A = All dates
B = =if(countif(DataDates,A1)>0,a1,"") <---Check if date is included in your data, if yes use that date, else ""
C = =iferror(index(DataValues,match(a1,DataDates,0)),na()) <--If it has a date, use the value, else #N/A

Column A is essentially a 'helper' column with all 365 days. Column B will be you're x-axis (This way the chart only shows labels for the dates you have in your data to show). Column C will be the values. I'm pretty sure there's an option to 'skip' the #N/As so your Feb and Nov lines will connect... (if that's not an option, you might have to add to the formula to create the slope of the line)

Hope that makes sense, I know it's kind of abstract.

 

[NotSparta]

Thanks!
It was a little to difficult for me but made me think in another way and finally finally solved it!

Column A has cells formatted as Date. I type in the date there. (ie A1: 2014-01-01)
Column B has cells formatted as Numbers. In B1 I type in =A1 . This transform the date to excels date number, in this case 41640 (for 2014-01-01).
Then I use Column B as x-axis.
On the chart, edit the x-axis to use Date. Voilà!

Really really weird excel doesn't transform date to date automatically by using the excel date number in the background.


Thanks for your help and your time!