Creating a Scatter or Line Graph Using a Wide Range of Dates

Before you can generate the chart, you must first generate the numbers you want in the chart. A pivot table or several COUNTIFS() or a FREQUENCY() function are probably good candidates for this part of the operation. I would expect you to end up with a table like

Please Login or Register  to view this content.

With a table like that, it should be as easy as adding a line chart (or scatter chart if desired) based on that table. A line chart will be able to use a nice date axis, which will probably look nicer for calendar dates, than the strict value axis that is available to a scatter chart.

Generating the table is probably the hardest part. Are you familiar with pivot tables? Are you familiar with the COUNTIFS() function?

I have very little experience with pivot tables, too. On the few occasions when I have used a pivot table for something like this, I ended up adding helper columns to the raw data table that would extract month, day, and year information from the date columns

Please Login or Register  to view this content.

With these helper columns added, it should be fairly easy to use a pivot table to count month, year, or month/year hires and separations.

For help on the various date functions used, or to explore using the COUNTIFS() function, this page links to help files for all of Excel's built in functions (COUNTIFS() is under statistical functions). https://support.office.com/en-us/art...rs=en-US&ad=US