Using Dates-Times-Numerals In Charts

**IanDGreen** · 08-28-2011, 01:33 PM

I have two columns of dates (Columns A & B) (dd/mm/yyyy), two columns of times (Columns C & D) (11:30:00) and two columns of numerals (Columns E & F) (two digits maximum with no decimal point).

I wish to display the dates across an entire month, Irrespective of whether the date appears in columns A & B, and showing the totals of both columns E & F relevant to their respective times.

e.g. If on 23/11/2011, there were a total of 5 numeric entries for that date in Column E, they would display as 5 items but corresponding to the individual times for each'
A further comparison display would then be shown, but based on the numbers and times contained in Column F.

I hope this clear, as my head is already muddled from many attempts at producing it.

Is there anyone out there who can tell me if this is an impossible task please.

Many thanks

Ian

**MarvinP** · 08-28-2011, 05:52 PM

Hi IanDGreen

This sounds like a pivot table answer to me. Sometimes I've had to "pad" my data to include all dates with no values (in E & F) to make it show the way I want.

We could give a better answer if you'd supply a sample database. You can do that by clicking on Go Advanced below the message area and then on the Paper Clip Icon above the Advanced Message area.

**IanDGreen** · 08-28-2011, 08:59 PM

Hi MarvinP

Now you HAVE taken me into a whole new world.
I have never attempted a Pivot Table before.

I have done a sample of the data, as you requested, and hope it is in the form you require.
Please let me know if it is not, but this basically how I shall be presented with the data.

Thanks for your interest in my problem.

Ian

**MarvinP** · 08-28-2011, 10:06 PM

Hi,

See the attached. I don't know how you want to display the two dates and two times. I'm feeling you want to do a round down to closest hour or half hour for the times so the sums make more sense.

**IanDGreen** · 08-29-2011, 07:55 AM

Hi MarvinP

Very many thanks for what you have done.

I have only downloaded at this stage, but will have a look at it and get back to you later today.

Again, my thanks

Ian

**IanDGreen** · 08-29-2011, 04:44 PM

Hi MarvinP

In view of your comments regarding the display of dates etc, I have had a rethink of the whole thing.

I think now that, if a time (In IG Reference in attached .jpg) falls between e.g. 00:00 and 01:00 it recieves a value of 1.
The total of all values falling within this timescale, and the corresponding date being equal to a particular date, the result should be displayed as a column, as shown on the attached.

The same would then be applied over the full 24 hour period x full calendar month, and obviously for both IG and SM data.

Would this still be a Pivotal Table application, or more a Charting one ?

Many thanks,

Ian