finding next business day of end date

A

ayl322

Can anyone help me with this?

If I enter a date, for example, 5/20/04 in cell A1, I want cells A2 to
A11 to show the same date in sucessive months (6/20/04, 7/20/04, etc)
such as with the edate() function.

But if that date happens to be on a weekend or a holiday,
I want it to give me the next business day.

so for June, it would give 6/21/04 since 6/20 is a sunday.
for February, it would give 2/22/05 since 20th is a sunday and 21st is
a holiday.

any help would be greatly appreciated!
 
P

Peo Sjoblom

I believe that will exclude all weekend days in between, if the OP wanted
06/21/04 from
05/20/04 that I assume he/she wants a workday only if the date + 1 month
falls on a
weekend day. Here is one attempt assuming one would copy down from A2

=DATE(YEAR($A$1),MONTH($A$1)+ROW(1:1),DAY($A$1))+CHOOSE(WEEKDAY(DATE(YEAR($A
$1),MONTH($A$1)+ROW(1:1),DAY($A$1)),2),0,0,0,0,0,2,1)

obviously it wouldn't take care of holidays

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
F

Frank Kabel

Hi Peo
good point. To take care of holidays the following should work:
=WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+ROW(1:1),DAY($A$1)-1),1,list_of_ho
lidays)
and copied to the left

The only thing that won't work if you choose a date like 31-Jan-2004 as
this would result in 2-March-2004 for the second month
 
Top