tracking # of employees quarter over quarter

Nat

I'm trying to show on a graph how many employees we had in each quarter. How would I do that?

My initial idea is to add a helper column for each quarter, and add a formula to return a 1 if any of the date range of that quarter falls after their start date and before their end date, then pull a report counting the 1s for each quarter.

That's already a problem because of rehires. Those may have to be fixed manually.

After this my brain grinds to a halt.

Has anyone successfully charted something like this?

Paul Newcome

Do you also track the end date of employment for those that have left?

Nat

Yes, I have start and end dates, and a formula that calculates current tenure if there's no end date, or total tenure if they have left. I also have active/inactive which is currently a pull down but could be automated based on an end date being put in, and I have a "today's date" column.

I'm not sure if I should be looking into creating something like the task rollup where there's a space waiting for a specific "today's date" and their active status to line up and be counted. I haven't been quite so stumped before, perhaps this is simply something I need to have them capture manually each quarter.

Paul Newcome

Here is my suggestion...

You would create a metrics sheet that starts off with two date columns. The first would be Quarter Start and the second would be Quarter End. Initially we will manually enter the start and end dates for a few quarters to make sure this is all working as expected. Then we can explore automating the quarter dates later.

Next, in the third column of your metrics sheet you would use

=COUNTIFS({Working Sheet Start Date}, @cell<= [Quarter End Date]@row, {Working Sheet Termination Date}, OR(@cell = "", @cell>= [Quarter Start Date]@row)

The above should get you your counts. Once we confirm this part is working, we can try to automate the quarter dates, and (if you are planning on showing this in a chart on a dashboard) we can work on getting some labels there in the primary column for you.