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

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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### floydschoice

##### New Member
Just a correction the date 19-OCT-29 should actually be 19-OCT-29 in line item 4 after the headers
 Zack Genozome 27-SEP-19 19-OCT-29 Exited NA

Replies
1
Views
195
Replies
10
Views
210
Replies
1
Views
37
Replies
3
Views
240
Replies
3
Views
75

1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

### 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