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

Status
Not open for further replies.

#### floydschoice

##### New Member
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?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Fluff

##### MrExcel MVP, Moderator
Status
Not open for further replies.

Replies
1
Views
135
Replies
10
Views
221
Replies
3
Views
70
Replies
1
Views
68
Replies
6
Views
83

1,128,154
Messages
5,629,011
Members
416,358
Latest member
grsaltzman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back