Hello,
I have the following Exit History data showing which year(s) an employee left the company. For this example, the assumption is that even though the employee left, they returned the next year and continued to work with the company until today. This can happen multiple times as it did with Tom.
Name | Exit Year |
Tom | 2009 |
Tom | 2011 |
Tom | 2015 |
Sally | 2010 |
I have a History table that shows the individual's data from 2007 to the present. Using that History table, I am trying to calculate how long it was from the year (RowYear) until their Exit. Here is what I am trying to achieve:
Name | Row Year | Years from Exit |
Tom | 2007 | 2 |
Tom | 2008 | 1 |
Tom | 2009 | 0 |
Tom | 2010 | 1 |
Tom | 2011 | 0 |
Tom | 2012 | 3 |
Tom | 2013 | 2 |
Tom | 2014 | 1 |
Tom | 2015 | 0 |
Tom | 2016 | - |
Tom | 2017 | - |
Tom | 2018 | - |
Sally | 2007 | 3 |
Sally | 2008 | 2 |
Sally | 2009 | 1 |
Sally | 2010 | 0 |
Sally | 2011 | - |
Sally | 2012 | - |
Sally | 2013 | - |
Sally | 2014 | - |
Sally | 2015 | - |
Sally | 2016 | - |
Sally | 2017 | - |
Sally | 2018 | - |
What would be the best way to do these calculations?
I tried the multi-row calculation and got pretty close, but the IF statement was getting unwieldy.
Thank you for the help.
Solved! Go to Solution.
Ben,
Thank you for sending this. It worked very well for me.
G
Ponraj,
I really love this solution! It was clear and easy for me to understand.
Thank you!
G