Calculate dates based on complex conditions and produce an outcome- [Excel 2013, VBA/Formula]

[floydschoice]

I have a few columns as below

Name	Company	Date of employment	Date of transfer	Employment status	Threshold
Dory	Walmart	27-SEP-19	19-OCT-19	Exited
Dory	Walmart	01-NOV-19	01-JAN-20	Exited
Dory	Walmart	27-JAN-20		Active	24-MAY-20
Zack	Genozome	27-SEP-19	19-OCT-19	Exited
Zack	Genozome	24-NOV-19	01-JAN-20	Exited
Zack	Genozome	13-JAN-20	01-FEB-20	Exited
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?

 

[Fluff]

Duplicate Calculate the difference between multiple dates based on certain conditions [Excel 2013 Formula/VBA]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).