Analysing time and days patterns in a chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raja - 20 Jan 2008 12:53 GMT

Hi there

What do you think would be best chart format to analyse some data that
consists of:
Date of Call out
Day of call out
Time Of Call out
Call out Reference

I am trying to see if there is any pattern in the days & times of call outs
to improve rosters.

Basically I want the time of callout & day of week mapped across
dates/months to see any trends.
For example if Fridays at 1am are a recurring callout time (after the pub!)
or every other Friday etc.

Do yo uthink Pivot tabe and charts is relevant or can normal chart provide
me with the results?

Many thanks

Reply to this Message

Christina - 23 Jan 2008 05:29 GMT

I have the same task. I would like to plot both date and time on the x-axis
to track deviations in data over time. My problem is I have the date and the
time in two different columns foor the incoming data. How can I make these to
variables appear in the same cell?

Christina

> Hi there
>
[quoted text clipped - 17 lines]
>
> Many thanks

Reply to this Message

Del Cotter - 23 Jan 2008 10:29 GMT

>I have the same task. I would like to plot both date and time on the x-axis
>to track deviations in data over time. My problem is I have the date and the
>time in two different columns foor the incoming data. How can I make these to
>variables appear in the same cell?

Dates are stored in Excel as whole numbers, and times as fractions of a
whole number. If you ensure that your dates are integers (i.e. there is
no time in them) and your times are always less than one (i.e. there is
no date information in them), then you can create a combined date and
time simply by adding the two numbers.

Examples:

Number Date or time Date and time
0.425 10:12 10:12 00 Jan 1900
39470 23/01/2008 00:00 23 Jan 2008
39470.425 23/01/2008 10:12 23 Jan 2008

Notice how the time appears to be on the "date" the noughth of January
1900. This is the starting point of the Excel calendar system. Today it
has been 39,470 days since then.

Now you can use the full number in a scatter (XY) chart to graph your
dates and times. Sadly, you can't use line charts, even with the
"Timeline" axis option, because timelines for some reason only operate
as integers, i.e. whole days, no times. However, you can very easily
format scatter series with lines, so that's not a problem.

Signature

Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
which goes to a spam folder-- please send your email to del3 instead.

Reply to this Message