Calculating Tenure based on Start Date & End Date

[spapay]

Hi there,

I need help with calculating tenure by month based on start date & end date provided. Please find below a screenshot of the chart I have to fill in, including my current attempt at a formula that is giving me the wrong answers but im not sure why.

=COUNTIFS(Data!$D:$D,">="&DATE(2015,2,1),Data!$D:$D,"<="&DATE(2018,1,31))+(COUNTIFS(Data!$E:$E,""<=""&DATE(2020,2,28),Data!E:E,""))

From this I am trying to get a count of employees who are still employed in the month February but have 2-5 years of tenure (hired between Feb,1, 2015 - Jan,31, 2018). It is pulling the correct # but it is not excluding the person that left the company on Feb 7, 2020 ( still including the people who left after the month of February) & I am not sure why. Please see snapshot below of the chart I have to fill in + an example of the format of the data attached.

Thanks in advance!
Capture.JPG

[Richard Buttrey]

Please upload the workbook that contains the chart and manually add some example results.

[spapay]

Thanks for your reply!

Please find attached an example with the chart & formula included.

As you can see it's providing a count of all entries that lie within the date ranges I outlined in the first 2 criteria of COUNTIF however I need it to exclude any rows which heave a departure date of 2020-02-28 or earlier.

[JohnTopley]

Try

COUNTIFS(Sheet2!$D:$D,">="&DATE(2015,2,1),Sheet2!$D:$D,"<="&DATE(2018,1,31),Sheet2!$E:$E,">"&DATE(2020,2,28))

[Richard Buttrey]

There are 4 people who left before Feb 29 2020. Should they all be excluded?

And would you indicate the actual results you expect to see for each of the tenure periods.

I've added a different generalised formula. When I understand the precise results you need we can work out a modification

[spapay]

Thanks so much for the work on this, we are close. The 4 people who left before Feb 29, 2020 should all be excluded.

I have manually done that on the chart, please see screenshot below for final tally:

Capture.JPG

[Richard Buttrey]

I'm struggling to understand which departure dates in column E meets the need to reduce the numbers in the last three tenure bands, i.e. currently 2,3,1 becomes 1,1,0 - a deduction of 1,2,1

Can you explain how you get the 1,2,1 deductions.

[spapay]

Yeah for sure;

So in total there were 4 people deducted since there were 4 people with departure dates prior to Feb 28, 2021. Please see screenshot below for the 4 individuals who left prior to Feb 28.

Which row I deducted them from depended on how much tenure they had:

Employee# 178 had 5+ years of tenure so I deducted 1 from row 11.
Employee# 137 had 1-2 years of tenure so I deducted 1 from row 9.
Employee#'s 185 & 148 had 2-5 years of tenure so I deducted 2 from row 10

Therefore the final headcount for tenure includes only current employees who have not yet left.
Capture.JPG

[josephteh]

Added 2 columns for the "Tenure Start Month and " Tenure End Month", formula for cell D3 as follows:
(unable to attach formula due to firewall)

Calculating Tenure based on Start Date & End Date.png