How To Add A Month To A Date

[tonguencheek]

Hey all. I'm looking for a way to add a month to date so that it returns the same day of the next month, unless it falls on a weekend or holiday it would move to the next good business day.

So for example, if in A1 I have 06/10/2011, the formula in B2 would return 07/11/2011 (since 07/10 is a Sunday).

Thanks in advance!

 

[barry houdini]

Try EDATE to add a month and WORKDAY to make sure it's a business day

=WORKDAY(EDATE(A1,1)-1,1)

 

[tonguencheek]

Thanks! How do I define which days to include as holidays?

 

[barry houdini]

If you list holiday dates somewhere on the worksheet, e.g. in H2:H10 then you can use that range in the WORKDAY function to exclude those too, i.e.

=WORKDAY(EDATE(A1,1)-1,1,$H$2:$H$10)

.....or name that range, e.g. holidays and use

=WORKDAY(EDATE(A1,1)-1,1,holidays)