Brain Teaser!!!! Need Help

[BradyA]

Hi All,
I need real help, I need a formula to calculate future date. The trick is the future date will depend on a couple of factors.
I am in recruitment, I recruit jobs and base them on categories
all jobs I work fall into 5 categories, 1-5 depending on how difficult they are to fill

Category 1 – takes 21 amount of days to hire
Category 2 – takes 35 amount of days to hire
Category 3 – takes 60 amount of days to hire
Category 4 – takes 84 amount of days to hire
Category 5 – takes 112 amount of days to hire



I want to calculate the start dates of each job I am working, depending on two things, what category it is, and where I am in the process with it, there are 4 stages to the process, Sourcing, Interview and Offer.
If a role is at Manager Meeting I want to add 63 days
If a role is at sourcing I want to add 63 days
If it is at Interview I want to add 42 days
If a role is at offer I want to add 35 days all to days date and the to the time it will take for the category.
In addition and if that wasn’t hard enough I want roles that are category 3-5 to have an extra week added to them when they get to interview stage, and subsequently offer stage.
Is this possible or does it make any sense to anyone!

 

[Wret]

Make a list of your categories, roles, stages or other variables and assign them a value in days. Set up your worksheet with extra columns where you can add references to your variables. Then use a formula like =LOOKUP(A2,Sheet2!A:A,Sheet2!B:B)+LOOKUP(C2,Sheet2!A:A,Sheet2!B:B)+NOW() to calculate the date.