Calculate workdays based on lead time

[snejsnej]

Hi,

I have a base date (in the example below "$I$4) from which I would like to subtract a lead time (in days). The output should be the date at which a particular task must be started in order to complete it by the base date deadline.

This is what I have, and it seems to work fine, but I'm worried that there may be a case (excluding holidays), where the (H13*7/5) may throw the dates off:

=IF((C13="Y"), IF(H13<>"",+$I$4-(H13*7/5),""),"")

Can this be improved upon?

Thanks,
Jens

 

[shg]

Perhaps

=IF(AND(C13="Y", H13<>""), WORKDAY($I$4, -H13), "")


WORKDAY will also accept a range containing holidays.

 

[JB_Scotland]

If you dont have the WORKDAY() function use INT(H13/5)*7+MOD(H13,5) in place of your H13*7/5

 

[jim may]

I tried using the Goal-Seek.. (in xl2007) feature - Data, What If Analysis... Goal Seek
and it seemed to work fine. I started ONLY with the following...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1											Holidays
2											5/31/2011
3					Must Start By				Must Have By:		6/9/2011
4				Guess Value to Begin ->>	5/1/2011				6/15/2011
5
6					31	<< Lead Time (In Days) Required
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E6	=NETWORKDAYS(E4,I4,K2:K3)

then select cell E6 - From Data Tab, What If Analysis, Goal Seek
Set Cell - enter E6
To Value - enter say 20
By Changing Cell - enter E4
OK,

Your sheet should now look as follows..
Note the Value in Cell E4 AFTERWARDS...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1											Holidays
2											5/31/2011
3					Must Start By				Must Have By:		6/9/2011
4				Guess Value to Begin ->>	5/17/2011				6/15/2011
5
6					20	<< Lead Time (In Days) Required
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E6	=NETWORKDAYS(E4,I4,K2:K3)

 

[snejsnej]

Thanks everyone, much appreciated.