date output

[excellentexcel]

hi guys
i have this problem
in cell d20 i have a contract date
in cell d21 i have a lead time
this will always be in the format eg(2-3 weeks)
in cell d22 i would like it to tell me d20 plus the first number of weeks ( before the hyphen)
and in d23 the second number of the weeksafter the hyphen
if you could help it would be great

[NBVC]

Try:

=D20+LEFT(D21,FIND("-",D21)-1)*7

and

=D20+MID(D21,FIND("-",D21)+1,FIND(" ",D21)-FIND("-",D21))*7

[Roger Govier]

Hi

in D22
=D20+LEFT(D21,FIND("-",D21)-1)*7

in D23
=D20+MID(D21,FIND("-",D21)+1,2)*7

I and assuming there will never be more than 99 weeks, and that there will always be a space between the the last number and the "w" of weeks

[excellentexcel]

thanks nbvc
is there anyway to make this a little more advanced and return the week commencing date

ie if the answer is 19th may 2009

it returns w/c 18/05/09

thanks

[NBVC]

Try:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[Roger Govier]

thanks nbvc
is there anyway to make this a little more advanced and return the week commencing date

ie if the answer is 19th may 2009

it returns w/c 18/05/09

thanks

D22
="w/c "&TEXT((D20+LEFT(D21,FIND("-",D21)-1)*7)+2-WEEKDAY((D20+LEFT(D21,FIND("-",D21)-1)*7)),"dd/mm/yy")

and
D23
="w/c "&TEXT(D20+MID(D21,FIND("-",D21)+1,2)*7+2-WEEKDAY(D20+MID(D21,FIND("-",D21)+1,2)*7),"dd/mm/yy")

[excellentexcel]

superb
thanks very much

[NBVC]

Well, yes, if you want the "w/c" in the cell too.. you would have to add the TEXT() function around MY formulas to get the date to look like a date rather than a serial number....