SOLVED

Find Start and End Date for Salaries in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2322036%22%20slang%3D%22en-US%22%3EFind%20Start%20and%20End%20Date%20for%20Salaries%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322036%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20pivot%20table%20of%20salaries%20of%20employees.%20I%20use%20this%20pivot%20table%20to%20find%20when%20the%20employee%20started%20and%20possibly%20ended.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20only%20count%20an%20employee%20when%20he%20or%20she%20was%20actively%20employed%20(when%20the%20salary%20isn't%20zero).%20I%20have%20employees%20who%20have%20been%20employed%20since%201%2F1%2F2021%20and%20stay%20the%20entire%20year%2C%20some%20that%20join%20during%20the%20year%20and%20stay%20until%20the%20end%20of%20the%20year%2C%20some%20employees%20that%20join%20during%20the%20year%20and%20leave%20during%20the%20same%20year%20not%20making%20it%20until%20the%20end%20of%20the%20year%2C%20and%20some%20that%20join%20for%20a%20single%20or%20December.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20want%20to%20get%20a%20count%20of%20when%20they%20actually%20where%20there%20based%20on%20being%20active.%20I%20previously%20attempted%20to%20count%20with%20a%20SUMIFS%20based%20on%20the%20dates%20of%20the%20year%20when%20salary%20isn't%200%20(SUMIFS(%24I%242%3A%24T%242%2CI3%3AT3%2C%22%26gt%3B0%22)%20to%20get%20a%20value%20at%20the%20end%20of%20the%20year.%2078%3Dfull%20year%20and%20anything%20less%20means%20fewer%20months.%20I%20did%20find%20an%20issue%20with%20this%20because%20if%20an%20employees%20worked%20January%20to%20February%20that's%20the%20same%20sum%20of%203%20as%20if%20the%20employee%20worked%20only%20March%20since%20it%20sums%20as%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20do%20I%20find%20the%20start%20and%20end%20of%20when%20the%20start%20month%20is%20and%20the%20end%20month%3F%20Example%3A%20I%20want%20to%20now%20for%20the%20second%20employee%2C%20He%20has%20a%20salary%20in%20Jan-March%20and%20it%20ends%20there.%20Not%20sure%20how%20to%20find%20the%20range%20of%20when%20he%20was%20active%20only.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot1.png%22%20style%3D%22width%3A%20689px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277873i3BBE7A3F71D85661%2Fimage-dimensions%2F689x273%3Fv%3Dv2%22%20width%3D%22689%22%20height%3D%22273%22%20role%3D%22button%22%20title%3D%22Screenshot1.png%22%20alt%3D%22Screenshot1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2322036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2322152%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Start%20and%20End%20Date%20for%20Salaries%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20count%20the%20number%20of%20months%2C%20enter%20%3DCOUNTIF(I3%3AT3%2C%22%26gt%3B0%22)%20in%20U3%20and%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2322498%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Start%20and%20End%20Date%20for%20Salaries%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20generate%20in%20parallel%20one%20more%20PivotTable%20with%20months%20aggregating%20them%20with%20min%2C%20max%20and%20count%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20862px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277918iE069432733396CEB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2323386%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Start%20and%20End%20Date%20for%20Salaries%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2323386%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%3C%2FLINGO-BODY%3E
Contributor

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. 

 

Screenshot1.png

3 Replies

@Jpalaci1 

To count the number of months, enter =COUNTIF(I3:T3,">0") in U3 and fill down.

best response confirmed by Jpalaci1 (Contributor)
Solution

@Jpalaci1 

As variant you may generate in parallel one more PivotTable with months aggregating them with min, max and count

image.png

Thank you so much!