MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Plotting Dates vs. Dates


Posted by Rick Coddens on January 08, 2002 1:54 PM

I'm trying to create a graph in Excel that plots Dates on both X and Y axis. When I do this, I also want to be able to format the axis to my liking. MS Excel allows me to format the X-axis with a scale resembling dates (i.e., put the units in days, weeks, months, etc.). BUT, it does not allow me to format the Y-axis in a similar manner. Even though the data being graphed and the Y-Axis are formatted as dates, it still wants the scale of the Y-axis to be number-like. I want the scale of the Y-Axis to be date-like. How can I do this.

By the way, please don't ask me why I'm needing to plot Dates vs. Dates. I just do!!!


Posted by Mark W. on January 08, 2002 2:15 PM

Suppose your dates in A1:A6 are...

X,Y
1/8/02,1/26/02
1/15/02,1/9/02
1/8/02,1/29/02
1/15/02,1/30/02
1/22/02,1/14/02

Without selecting any data create a blank XY
(Scatter) chart type using the Chart Wizard.
Select this new object and paste the formula,
=SERIES(,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1),
into the formula bar. Voila!