Hi,
Employees are often rehired and I need to determine which start date to recognise as 'continuous service' for the purpose of service awards.
Prior start dates are only recognised as continuous where the break in employment is 90 days or less.
I need to determine which start date to recognise and then calculate service in years and months based on this identified start date.
Some employees have 2 employment history lines and some have up to 5 lines.
Below 3 employee examples.
By manually reviewing the data I know the following answers but i need a formula to apply
Employees are often rehired and I need to determine which start date to recognise as 'continuous service' for the purpose of service awards.
Prior start dates are only recognised as continuous where the break in employment is 90 days or less.
I need to determine which start date to recognise and then calculate service in years and months based on this identified start date.
Some employees have 2 employment history lines and some have up to 5 lines.
Below 3 employee examples.
By manually reviewing the data I know the following answers but i need a formula to apply
- Employee 1 - 25/6/2018 is the continuous service start date
- Employee 2 - 27/11/2017 is the continuous service start date
- Employee 3 - 5/09/2014 is the continuous service start date
| ID | DateJoined | TERM DATE | START DATE TO RECOGNISE AS CONTINOUS SERVICE | TOTAL YEARS SERVICE BASED ON START DATE RECOGNISED |
1 | 4/09/2004 | 16/01/2015 | ||
1 | 25/06/2018 | 20/03/2019 | ||
1 | 17/01/2022 | |||
2 | 27/11/2017 | 9/03/2018 | ||
2 | 10/03/2018 | |||
3 | 19/11/2010 | 26/08/2011 | ||
3 | 22/11/2011 | 22/01/2013 | ||
3 | 5/09/2014 | 5/04/2016 | ||
3 | 6/05/2016 | 21/07/2017 | ||
3 | 12/09/2017 |
