chart in excel 2003 with vba


I'm having trouble creating the a chart with this data:
A      B      C
date +  time      data

01.01.2012 12:00      12:00            456789

There are several sets of these data. Date and time is always unique, but time is repeating each day. Data consists out of numbers.

I need to have date only on the right side (legend entries), and time (which can be used from first or second column) on the x-axis. So the resulting chart would display for each day a data line.

This problem is how to extract the date only, second problem is how to put the time entries on the X axis?

What is the best way to do this in VBA without using pivot? I'm using Excel 2003.

Thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I'm trying (and failing) to picture the chart you want to create. If it can't be done manually, then it won't be doable with VBA either.

If you want the date in the legend, then each row will need to be a series.
If each row is a series, then there won't be a line, since it's only one data point per series.
If the time is the same value every day, then you won't see a line, either, since all data points will sit at the same spot.

Post a sample file with a few rows of sample data and let's try to create a meaningful chart manually. Once that has been worked out, it can be done with VBA, too.

But there's no use trying to write code for a chart that does not seem to be possible in the first place.

cheers, teylyn
Hi Fernando,

I was trying to visualise what you were trying to build.  If the date and time are always unique, the 'second time' repeats each day, and the data (your figure 456789 for example) is the other data series, then the date/time + time, and the raw data would seem to be the appropriate data series for your line graph.

In Excel I mocked up a data series for a stock price per day, where the value is captured at set times per day, as follows:
Date              Time    Stock Price
01/01/13       09:00    200
01/01/13       12:00    201
01/01/13       17:00    205
02/01/13       09:00    199
02/01/13       12:00    200
02/01/13       17:00    203   et cetera...

I then copied the data range, and did a Paste Special... Transpose, so that the data came out 'pivoted':

Date                01/01/13   01/01/13   01/01/13
Time                09:00        12:00         17:00
Stock Price       200           201            205

I then selected the pivoted data range, and generated a chart based on that data.  I ended up with a line chart that had the stock price on the y-axis and the combination of date and time on the x-axis (see attached file as an example).

As I was able to do this manually using the above steps, all you need to do is repeat the process with the Macro Recorder switched on, and that will give you the 'barebones' of some VBA code you can use to achieve your goal...  providing I understood your requirements correctly!!
Fernando2323Author Commented:
hello armchair_scouse

thanks for your reply and efforts. Regarding the raw data this is correct. But as I wrote the chart should display a line for each day, so you can easily compare each day for example the stock price on Monday eight o'clock compared to Tuesday eight o'clock as well. The problem is, the X axis should be of limited range displaying each hour of today only once if there is data available. I will try to provide an example later.
Thanks in advance.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Try the example above... each data series represents one day, with stock prices changing every hour between 9:00 and 17:00.  Macro behind the sheet to build the chart.  I recommend stepping through it, as when I tested it, Excel worked out exactly wheat I needed after just a few lines.
Fernando2323Author Commented:
Great this looks very good. But still there is one problem: ( how can I transform the original data into the format in which you were using now? Can this be done with transforming or is there any additional calculation necessary?
To transform the data from my first suggestion (on StockPrice.xls) to the second suggestion (StockPriceSeries.xls) would need some VBA, Transpose on its own would not be able to do that as the second format does not repeat the time headers, it only has them once.

I've written a routine that will do the job, TransformDataSeries, in the attached file.  Not perfect - I'm writing this after waking up in the early hours and not being able to get back to sleep!!  That's my excuse anyway...   :o)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando2323Author Commented:
Thanks a lot! Appreciate your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.