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

You will want to start your formula with an IF.

=IF(TODAY()>= [Quarter Start Date]@row, COUNTIFS(..........), 0)

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.

Nat

Thanks so much! This does count how many are active each quarter.

However it's showing the current quarter's active employees for future quarters as well, and I would like those to be blank until the quarter starts.

I added the quarter labels, and pulled a report from it which filters results for "is greater than 0" - if there's a way to make future quarters be 0, or automate their appearance as you suggested, the chart should stay current on the dashboard.

Paul Newcome

You will want to start your formula with an IF.

=IF(TODAY()>= [Quarter Start Date]@row, COUNTIFS(..........), 0)

Nat

That worked fabulously, thanks so much!

Paul Newcome

Happy to help. 👍️

Nat

As long as today is higher than the start of this quarter (ie omit future quarters): (TODAY() >= [Quarter Start]@row,

Count rows where the start date is lower than the end of this quarter: COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row,

And the Date of Departure is higher than the start of this quarter, or empty: {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row)))

@Paul Newcome did I get this right? I now have to replicate a similar graph for turnover, hire types, etc etc etc so I want to make sure I understand what you did here :D

Paul Newcome

@Nat That's it exactly.

Nat

Thanks Paul! It's sinking in! Wow, formulas are so hard to get your head round but I now have hope.

In the name of being useful to anyone else working on this - I used the same metric sheet to get the turnover like this... hopefully these are pulling up what I think they are!

departures: =IF(TODAY() >= [Quarter Start]@row, COUNTIF({HR mock-up sheet DATE OF DEPARTURE}, >[Quarter Start]@row))

turnover: =IF(TODAY() >= [Quarter Start]@row, Departures@row / [Active EEs]@row) (and I made this column a percentage)

new hires internal: =IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, <=[Quarter End]@row, {HR mock-up sheet START DATE}, >=[Quarter Start]@row, {HR mock-up sheet HIRE TYPE}, "Internal"))

and so on

Paul Newcome

@Nat No worries at all. If you break down what you need into small pieces, work on one piece at a time, and then put it all together, it might surprise you what you can accomplish. Sometimes trying to build it all out at once can get you thinking in circles.