Analysing time and days patterns in a chart

[Raja]

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

 

[Christina]

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

 

[Del Cotter]

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.