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

Status
Not open for further replies.

floydschoice

New Member
Joined
Mar 30, 2020
Messages
5
Office Version
  1. 2013
  2. 2011
Platform
  1. Windows
I have a few columns as below

NameCompanyDate of employmentDate of transferEmployment statusThreshold
DoryWalmart27-SEP-1919-OCT-19Exited
DoryWalmart01-NOV-1901-JAN-20Exited
DoryWalmart27-JAN-20Active24-MAY-20
ZackGenozome27-SEP-1919-OCT-19Exited
ZackGenozome24-NOV-1901-JAN-20Exited
ZackGenozome13-JAN-2001-FEB-20Exited
ZackGenozome15-FEB-20Active21-JUL-20
AdamRazortech04-MAR-20Active30-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?
 

Some videos you may like

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.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top