Forum Discussion

Jpalaci1's avatar
Jpalaci1
Brass Contributor
May 04, 2021
Solved

Find Start and End Date for Salaries in Pivot Table

I created a pivot table of salaries of employees. I use this pivot table to find when the employee started and possibly ended. 

 

I need to only count an employee when he or she was actively employed (when the salary isn't zero). I have employees who have been employed since 1/1/2021 and stay the entire year, some that join during the year and stay until the end of the year, some employees that join during the year and leave during the same year not making it until the end of the year, and some that join for a single or December. 

 

I just want to get a count of when they actually where there based on being active. I previously attempted to count with a SUMIFS based on the dates of the year when salary isn't 0 (SUMIFS($I$2:$T$2,I3:T3,">0") to get a value at the end of the year. 78=full year and anything less means fewer months. I did find an issue with this because if an employees worked January to February that's the same sum of 3 as if the employee worked only March since it sums as 3.

 

How do I find the start and end of when the start month is and the end month? Example: I want to now for the second employee, He has a salary in Jan-March and it ends there. Not sure how to find the range of when he was active only. 

 

Resources