floydschoice
New Member
- Joined
- Mar 30, 2020
- Messages
- 5
- Office Version
- 2013
- 2011
- Platform
- Windows
I have a few columns as below
The above table shows example of temporary freelancer employment details of Dory, Zack and Adam. The "Employment Status" column indicates "Exited" for the past employment(s) of a person to a particular "Company" and "Active" if the same person is currently employed in the same "Company". The main condition here is that each person can be employed in the same company for a maximum of 8 months (or 30*8 days) only, subject to the below conditions:
1. Excel should calculate the threshold duration of 8 months for a person from the first date of employment in the same company which in the above case is 27-SEP-19 for Dory and Zack and 04-MAR-20 for Adam (Column "Date of employment").
2. However, if there is a difference of at least 1 month (or 30 days) between 2 consecutive employments in the same company for the same person (Date of employment minus previous date of transfer = or > 30 days) then excel should ignore the previous employments and start calculating the threshold date from the date of employment after the most recent break period has been achieved.
in the above table example, this is still going to be calculated from 27-SEP-19 for Dory since she does not have a minimum 1 month duration between her consecutive employments and excel should display 24-MAY-20 (i.e. 27-SEP-19 plus 8 months) in column "Threshold" in the same row as "Active" employment status but for Zack it will be calculated from 24-NOV-19 since he has a difference of at least 1 month (minimum 30 days) between his second and first employment and excel should display 21-JUL-20 (i.e. 24-NOV-19 plus 8 months) in column "Threshold" in the same row as "Active" employment status.
Now there could be many more companies that n number of persons could have worked for but I am only concerned where the person has an active employment status in a company irrespective of how many past employments they had in the same company or even if they didn't have any past employments in the company like in the case of Adam. At the end Excel should calculate the threshold for each person's employment in the same company subject to the above conditions and return the outcome in column "Threshold". I am relatively new to VBA and I have no idea how to make this work. Can someone help? If this can be achieved by just formula can someone show me how?
| Name | Company | Date of employment | Date of transfer | Employment status | Threshold |
| Dory | Walmart | 27-SEP-19 | 19-OCT-19 | Exited | NA |
| Dory | Walmart | 01-NOV-19 | 01-JAN-20 | Exited | NA |
| Dory | Walmart | 27-JAN-20 | Active | 24-MAY-20 | |
| Zack | Genozome | 27-SEP-19 | 19-OCT-29 | Exited | NA |
| Zack | Genozome | 24-NOV-19 | 01-JAN-20 | Exited | NA |
| Zack | Genozome | 13-JAN-20 | 01-FEB-20 | Exited | NA |
| Zack | Genozome | 15-FEB-20 | Active | 21-JUL-20 | |
| Adam | Razortech | 04-MAR-20 | Active | 30-OCT-20 |
The above table shows example of temporary freelancer employment details of Dory, Zack and Adam. The "Employment Status" column indicates "Exited" for the past employment(s) of a person to a particular "Company" and "Active" if the same person is currently employed in the same "Company". The main condition here is that each person can be employed in the same company for a maximum of 8 months (or 30*8 days) only, subject to the below conditions:
1. Excel should calculate the threshold duration of 8 months for a person from the first date of employment in the same company which in the above case is 27-SEP-19 for Dory and Zack and 04-MAR-20 for Adam (Column "Date of employment").
2. However, if there is a difference of at least 1 month (or 30 days) between 2 consecutive employments in the same company for the same person (Date of employment minus previous date of transfer = or > 30 days) then excel should ignore the previous employments and start calculating the threshold date from the date of employment after the most recent break period has been achieved.
in the above table example, this is still going to be calculated from 27-SEP-19 for Dory since she does not have a minimum 1 month duration between her consecutive employments and excel should display 24-MAY-20 (i.e. 27-SEP-19 plus 8 months) in column "Threshold" in the same row as "Active" employment status but for Zack it will be calculated from 24-NOV-19 since he has a difference of at least 1 month (minimum 30 days) between his second and first employment and excel should display 21-JUL-20 (i.e. 24-NOV-19 plus 8 months) in column "Threshold" in the same row as "Active" employment status.
Now there could be many more companies that n number of persons could have worked for but I am only concerned where the person has an active employment status in a company irrespective of how many past employments they had in the same company or even if they didn't have any past employments in the company like in the case of Adam. At the end Excel should calculate the threshold for each person's employment in the same company subject to the above conditions and return the outcome in column "Threshold". I am relatively new to VBA and I have no idea how to make this work. Can someone help? If this can be achieved by just formula can someone show me how?
