Excel 2016 Graphing help

It depends how you generate the column with the names of the days of the week.

If column A contains your dates and you create column B with the names of the days, the plot results will depend on how you create column B; eg.

1. Bi = Ai formatted as "dddd" (gives Sunday, Monday, etc).

In this case your x-scale labels will show as 'Sunday', 'Thursday', 'Monday' ... etc or similar, but the x-value of each point plotted will be at the date number (DATEVALUE), not the day name ('Wednesday' etc). Consequently the data will be scattered at various points along the x-axis (ie. at the date number locations), not lined up in seven columns above each day label. The 'Sunday', 'Thursday', 'Monday' labels are the day names of the dates at the label positions. If you formatted column B as numbers, then the labels would show as numbers such as 42421 (= date number for 21/02/2016). Anyway, this is not what you want.

2. Bi = TEXT(Ai, "dddd") then you get column B filled with text values of the type 'Sunday', 'Monday', 'Tuesday', etc, which are the day names you want.

However, if you use these as the x-values in your 'scatter plot' the result will not be what you want/expect. Basically you will end up with a 'line plot' with each point plotted 1 increment along the x-axis. That is because a 'scatter plot' in Excel requires numerical data for both axes. If you attempt to plot a 'scatter plot' with text values for the x-axis, you effectively get a 'line plot', even though Excel shows it as a 'scatter plot'. You can confirm this by plotting the data as a 'line plot' and setting the line colour to 'None' (so only the points are visible). You will see that the points are in the same positions as you get for the supposed 'scatter plot'. The x-axis labels will show as day names but your points will not appear lined up with the labels. Again, not what you want.

3. Bi =WEEKDAY(Ai,1), formatted (eg. right click, Format Cells ...) as "dddd". The '1' in the function sets the week to start at Sunday.

This is what you want. You will again get column B filled with values of 'Sunday', 'Monday', 'Tuesday', etc but the underlying values are in fact 1,2,3, ... 7; ie. they are the day numbers in the week corresponding to your dates. They show as 'Sunday', 'Monday', 'Tuesday', etc due to the format applied. That does not change the underlying values - they are still numbers. So now you will get your data plotted at position 1,2, or 3 etc (up to 7) but x-axis will be labelled 'Sunday', 'Monday', 'Tuesday', etc in accordance with the format applied to the data in column B.

A couple of additional points. If you want only 'Sunday' - 'Saturday' displayed along the x-axis labels you will likely need to format the x-axis to have a Minimum Value of 1, and Maximum Value of 7. Otherwise you may (will?) get 'Saturday' (day 0) as the first label, and 'Monday' as the last one (day 8). Those days will have no data above them though.

Plotting this way with a large amount of data will result is many points overlapping or overlaid (eg. two points where the time is the same, or close to the same). In order to view of the density of calls vs time, you would need to apply transparency to the plotted points.

See example plot below: 70% transparency applied to marker Fill, 'No line' for marker Border. Depending on how many points you have it may be better to apply a higher level of transparency. Also had to apply a custom format to the Time scale.

Not certain whether/how can get top time label to display as 24:00? 'Best' I could do is set Maximum to 0.9999, which gives the top label as 23:59 but all the other labels are also one minute short of the hour. Not tidy! Better as below.

Note: these are dates and times of a couple of hundred earthquakes so not expecting any particular pattern here!

Shoes2468

mdf

TwoSeven

DS248

Shoes2468