How to search events between two dates in WP?

Charting dates in

• QUESTION: This is probably embarrassingly simple yet I can't even work out how to phrase a google search to find the answer! Basically, in Excel 2007, I wish to chart a data series that looks like: 01/01/2010 - 1 05/01/2010 - 3 15/01/2010 - 1 etc etc It is basically to chart no. of incidents by pupils at a school. Currently, for the life of me I can't get the missing dates to show as "Zero" - I'm sure I could do it in 2003 when I was last seriously playing with excel. So my line chart draws a line from 1, to 3 to 1 again, and charts the missing dates on the x axis, but doesn't drop the line to zero on those missing dates Any help appreciated! Thanks Pete ANSWER: Pete, right click on the chart in the menu popup choose select data this will bring up the "Select Data Dialog" on the bottom portion of this dialog is a button with the caption of Hidden and Empty Cells Click on this button. In the resulting dialog, make your choices on how you want the chart to behave that said, If I just have your 3 rows of data, the chart show 3 datapoints connected with a straight line whether I choose line chart of XY Scatter plot. So I assume you have more dates you didn't show with not data location where the Y value would be. ---------- FOLLOW-UP ---------- QUESTION: , sorry for the double follow-up, but I think that as the functionality I obviously dreamt up doesn't exist, I'm going to need a macro, and my skills are far too basic for what I'm thinking of. I wonder if it isn't too complicated for an expert whether you might be able to help? Basically, the data I showed you is in a pivot table which is sorting incidents (and lots columns of data relating to the incidents) by pupil name, and doing counts of incidents since there may be more than one incident per day. Currently users would enter dates into cells B1 and B2 in worksheet "Dashboard" and I'd chart the appropriate rows in the pivot table, but I need empty dates to be charted as zeros. So, instead, I now think I need to have a macro attached to a button that uses the dates input by the user (cells B1 and B2 in worksheet "Dashboard"), and to write those dates and all dates in between into column A of a spare worksheet. It then needs to look at the pivot table, which currently starts in Cell A10 of worksheet "Data extraction" (that's where the first row of data sits), and extract any data in the rows that are in between the two dates entered by the user, and then insert zero values into the dates that aren't in the pivot table. Does that make sense? Does this sound do-able without too many gazillion lines of code? Any help would be very much appreciated. Thanks Pete

• Answer:

  Pete, I believe putting the zero in the y value column is your problem for the chart. There isn't and option to tell the chart to ignore zero values. (after all, how would it know a zero is really a missing value or a value you don't want to plot). Those cells need to be blank. If you need the table to contain zeros, the data could be placed somewhere else - maybe even on a hidden sheet. In any event, if you want to send me the workbook I can take a look. I definitely can't write a macro without knowing where everything is and where you want things.