Add months with rules.....

[buddy1000]

I have this formula..

=WORKDAY(DATE(YEAR($Z$10),MONTH($Z$10)+6,DAY($Z$10)+0)-1,1,'Holidays'!A2:A60)

It looks at date Z10 and adds 6 months. If this results is a weekend or a holiday it returns the next workday. In column Z, I have different dates. Now I have to change the 6 to 12 to 18 months etc. etc.

Is it possible to alter it to do this automatic? I tried to use rOW but got lost a bit.

Any ideas?

 

[onlyadrafter]

Hello,

which cell is this formula in? and which cells do you want the 12 and 18 months to be in. I'm sure there is a way of using row. (there will be a different way for sure.)

 

[buddy1000]

Formula is in cell A2
add 12 months should be in A3
add 18 months should be in A4
add 24 months should be in A5
etc.
etc.

 

[onlyadrafter]

Hello,

in A2 enter

=WORKDAY(DATE(YEAR($Z$10),MONTH($Z$10)+(ROW()-1)*6,DAY($Z$10)+0)-1,1,'Holidays'!A2:A60)

and copy down as far as required.

Is this giving expected results?

 

[buddy1000]

PERFECT...
but now lets say that instead of A2, it should be in Z11.

What changes in the formula.

 

[buddy1000]

sorry..I should ofcourse just change () to A2.....

thanks

 

[barry houdini]

Perhaps use EDATE to simplify, e.g. in Z11 copied down

=WORKDAY(EDATE(Z$10,6*ROWS(Z$11:Z11))-1,1,'Holidays'!A$2:A$60)