Case study: staff turnover
From: Sofia Laine, HR business partner, Brightwater Labs (a fictional company)
About: Turnover by department
The staff list has everyone who worked here since 2019, with a leave date for people who have gone. For each person I need their status and years of service up to the as-of date in O2. For each department: headcount, leavers, turnover rate and how long leavers stayed on average. Then tell me where turnover is highest.
HR data is full of blanks that mean something: an empty leave date means the person still works there. That makes two patterns useful. IF(E2="", ...) tests for an empty cell, and IF inside another function picks the right end date for each person.
YEARFRAC(start, end) returns the years between two dates with decimals, which is what "years of service" usually means. Use a fixed as-of date rather than TODAY(), so the report gives the same answer tomorrow. The department summary then uses COUNTIF, COUNTIFS and AVERAGEIFS, with a rate to compare departments of different sizes.
All people and figures are made up.
=IF(E2="","Active","Left")
=YEARFRAC(D2,IF(E2="",$O$2,E2))
A status from a blank, and years of service to the right end date.