Hello,

Please have a look at this chart:



As you can see, the y axis is made from a row of dollar amounts and the x axis is made from dates in the row above it. I'm wondering if there's a way to make Excel a bit smarter when it comes to dates. What I'm looking for is a chart laying out the dates on the x axis but to scale with actual time. For example, you see the first three dates:

Feb 13 2017
Mar 17 2017
Mar 24 2017

They're all spaced evenly apart, but in actual time, Feb 13 and Mar 17 are more than a month apart, and mar 17 and mar 24 are only a week apart.

I'm wondering if Excel is smart enough to do something like this:





So I'm looking for a graph in which the months are to scale with actual time (equally spread out) and the dates fall where they would actually fall in actual time. The coloring of the months would be nice too but I don't need that. The dates on each data point would also be nice but I don't need that either. Just making the dates to scale is all I'm looking for.

Thanks

Right click on the axis. Under Axis Options:Axis Type, make sure it is set to Date and not Text. I would have thought this would be the default so maybe this is not the problem.

Are you still on Excel 2003 (as per your profile)? If so consider a x-y scattergram instead of a plain line chart.
postscript to the above: I note that B2 doesn't look like a proper Excel date due to the lowercase f. Your dates will need to be proper Excel dates for both gebobs' and my solutions to work.

Attached Images

Capture3.JPG (96.1 KB, 26 views)

It's Excel 2010 (I should really update my profile).

As you can see from the screen shot, the row of dates are set to Date format:



^ I right clicked on cell B1 and selected Cell Formatting.

I followed gebobs suggestion but that seemed to have no effect.

Will cells that are formatted for Date recognize their format as date if they're not entered right in the first place? Because the dates you see are exactly how I entered them. Perhaps it needs a specific format like 13-02-2017?

The cells can be set to any date format, if the cell doesn't contain a date you just see the text. The biggest clue from your screenshots that some of these are unlikely to be dates is that youv'e chosen a date format that should show the full month name yet the Sample field still shows a 3-letter month - a sure sign that Excel hasn't recognised it as a date. This is what it should look like:



To check if a cell has an Excel date (a number) in it, open the cell formatting dialogue box and choose General in the Category list and you should see a number in the Sample box:


(You don't need to close the box with OK, you can just Esc out of it without changing anything.)
In your case, I'd select all the dates in the top row, open the cell formatting dialogue box and convert them all to General. All the non-dates will show up as text, so you just need to re-enter those dates in an Excel-recognisable format. Excel will try to be helpful and if it recognises a newly-entered value as a date it will format it for you in some date format so to double check they're all dates again you'll have to convert them all to General again. Once they're all dates, format them as you will and your charts should be OK.

Attached Images

	Capture3.JPG (21.7 KB, 48 views)
	Capture3a.JPG (26.5 KB, 46 views)

Something is odd about those dates. If they are truly all formatted as date, why would the month be in lowercase as it is for feb 17, may 31, and jun 2? Some have commas between the date and year, others don't. Some have abbreviated months, others don't.

Yeah, I can't explain it.

I tried what you said, p45cal--I switched all the dates to general, and indeed some remained just the text of the date while others became numbers (the numbers I can't explain; for example, March 24, 2017 became 42818 <-- Huh!?). But trying to switch them back to date doesn't seem to work. I try all kinds of formats, hoping Excel recognizes at least one, but it doesn't.

Anyway, I'm attaching the file in case anyone wants to take a crack at it.

Thanks very much for trying to help.

Attached Files

money chart.xlsx (21.8 KB, 9 views)

It's the numbers you want. It's the number of days since 1st Jan 1900. I'll be in front of a machine in an hour or so and will have a look at your file.

In the attached:

• Sheet1:
  • row 1 dates all converted to Excel dates. I guess some are the wrong year (columns I, J, S & AC). Charts look fine.
  • I've added an X-Y scatter plot in the vicinity of cell C40
• Sheet2 (for your educonfusion):
  • row 1 is a copy of your original row 1
  • row 2 is another copy of that row, but formatted to General. Note that only a few cells show numbers (highlighted in green by me). These are what you want.
  • row 3 is where I've manually entered the dates (guided by the text in row 1), using a format recognisable to Excel. Once a date is recognised as a date by Excel as you enter it, it immediately formats that cell as a date based on your local settings. Once I'd entered all the dates, I reformatted row 3 to General, just to show that they're all numbers.
  • row 4 is a copy of row 3, but custom formatted to mmm d yyy.

Attached Files

MSOForums38019money chart.xlsx (23.8 KB, 10 views)

That's what I figgered.

Thanks p45, that's terrific! Thanks very much for putting in the time.