calculate workday before x month

[XLS-EXCEL]

Hi Everyone

I have a table of some vacancies for a company. I only require the staff for those vacancies to start working from a certain month, which is different for different employees. For example I might need one in Nov but the second member of team that I require I might not need until January 2008. Where I dont need the staff the data within that month column reads 0.0, where I need the staff part time it reads 0.5 or where I need them full time it reads 1.0.

However, It takes a certain number of working days to find an employee. This depends on the type of employee I want to find. The type of employee and the time it takes (the number of working days) are stated in a table underneath (second table).

Therefore I want to be able to know when I need to start looking for an employee in order to have them ready and employed and working for me as soon as the month in which I need them arrives.

e.g. if I need an employee in Nov 07 and that employee is a Contractor (and according to the table it will take 20 working days to arrange for that employee) I want excel to calculate a date (in colummn C) that number of days (20days in this case).

However, for example if its a permanent I want excel to calculate that date to 60 working days prior the month when they are needed. (as mentioned the days and type of employee are mentioned in the table in the worksheet, 4 maximum combinations).

The month from which I want them to start working from is indicated by placing a 0.5 or 1.0 in that column (cell). I am only interested in Excel working out a date x number of working days before the month in which I FIRST need them so please ignore any months with 0.5 or 1.0 thereafter. I just want it to look for 0.5 or 1.0 in whichever month comes first and calculate a date of when I should look for that employee based on the time it takes to find that type of employee as stated in the second table below.

I hope I have not confused you all. I am sure its simpler than I make it. I would really appreciate your help in this matter.

Many Thanks and Regards

[daddylonglegs]

It's going to be difficult to do with your current setup, especially with the year in a merged cell (not recommended for formulas).

Assuming D2:P2 contained actual dates, the 1st of the month, e.g. in D2 1st June 2007 [you can format these however you want, e.g. custom format mmm to show just the month] then you could use this formula in C3

=workday(INDEX(D$2:P$2,MATCH(TRUE,INDEX(D3:P3>0,0),0)),-VLOOKUP(B3,A$7:B$10,2,0))

copied down if you want. Note: you also need to change A8 so that it exactly matches B3, with spaces in the same places etc.

[NBVC]

See attached for example...

I hope I interpreted the question correctly..

Note: I changed the month column headers to actual dates and just formatted as "mmm". This is so I have actual dates to play with. I also assumed you are always go back from the 1st of any month.

Please note: You must be consistent with titles... the titles in your small table didn't match exactly the items in the drop down lists...

The formula I used in C7, copied down is:

=IF(ISNUMBER(MATCH(A7,$B$3:$B$5,0)),WORKDAY(INDEX($D$2:$P$2,MATCH(TRUE,INDEX($D$3:$P$5,MATCH(A7,$B$3:$B$5,0),0)>0,0)),-B7),"")

which must be confirmed with CTRL+SHIFT+ENTER as it is an array formula.

[daddylonglegs]

Note: I changed the month column headers to actual dates and just formatted as "mmm". This is so I have actual dates to play with. I also assumed you are always go back from the 1st of any month.

Please note: You must be consistent with titles... the titles in your small table didn't match exactly the items in the drop down lists...

Snap! same time too.......

[NBVC]

Snap! same time too.......

..what is it they say: "Great minds think alike..."

[XLS-EXCEL]

lol, great minds think alike indeed..

very nice guys. Thanks

Only thing is that the dates appear in the second table underneath the sheet NBVC attached and I wanted the dates to appear in column C (raise date column).

Cos everytime I select a role from the menu in column B, the date shows next to the type of employees in the table below (e.g. the date will appear next to the durations in column B7, B8, B9 or B10 rather than the RAISE DATE cell in Column C3, C4 or C5.

Ofcourse this must be cos of the way I initially explained it, so apologies for this small glitch....

Any Ideas?

Regards

[NBVC]

lol, great minds think alike indeed..

very nice guys. Thanks

Only thing is that the dates appear in the second table underneath the sheet NBVC attached and I wanted the dates to appear in column C (raise date column).

Cos everytime I select a role from the menu in column B, the date shows next to the type of employees in the table below (e.g. the date will appear next to the durations in column B7, B8, B9 or B10 rather than the RAISE DATE cell in Column C3, C4 or C5.

Ofcourse this must be cos of the way I initially explained it, so apologies for this small glitch....

Any Ideas?

Regards

Then I think you want daddylongleg's formula in C3, CSE confirmed and then copied down:

=WORKDAY(INDEX($D$2:$P$2,MATCH(TRUE,($D3:$P3)>0,0)),-VLOOKUP(B3,$A$7:$B$10,2,0))