Hey All,
Attached is a sample of data I'm looking to use to generate order dates based on delivery & lead times(in weeks). I will always know when I need a certain line item onsite; column M. I will always know a lead time; column K. I'd like to use column M and subtract the lead times to know when I need to place the order. The lead times can be a single number, a range(in which the higher number will be used), or a few numbers(i.e. 10,6, 15 in which the higher number will be used). I tried to generate a formula in I11(=TEXT(DATE(YEAR(M11),MONTH(M11),DAY(M11))-IF(ISNUMBER(K11),K11,MAX(IF(ISNUMBER(SEARCH("-",K11)),MAX(LEFT(K11,FIND("-",K11)-1),RIGHT(K11,LEN(K11)-FIND("-",K11))),K11)))*7,"MM/dd/yyyy")) but it doesn't work for single numbers. Can anyone help me get it work for almost any case and just select the largest or just use the single number in the cell? I also want to make sure the resulting day is a workday and keep in mind the lead times are weeks in cells of column K.
Much appreciated!
Bookmarks