finding next business day of end date

  • Thread starter Thread starter ayl322
  • Start date Start 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!
 
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
 
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
 
Using the Add-in "EDate" seems to help with the issue you describe.

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


As a side note, I would like to ask this question on the Workday function.
The very last line in Excel XP help on "Workday" says this...

"To convert the range of cells used for holidays in the last example into a
array constant, select A4:A6 and then press F9."

Is this a true statement? I've selected A4:A6 and hit F9, and nothing
happens. I know that F9 will evaluate an expression while in Edit mode.
Does anyone know what this help topic is refering to? Thanks.

Dana DeLouis
 
Hi Dana
and a solution without using the ATP could be:
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+ROW(1:1),MIN(DAY(A1),DAY(DATE(YEAR(A1)
,MONTH(A1)+ROW(1:1)+1,0))))-1,1,list_of_holidays)

For your other question: Yes this works for me (Excel 2003). To achieve
this I tried the following:
- Enter the formula like
=WORKDAY(A1,5,E1:E10)
- then I select in the formula bar the part 'E1:E10'
- now hit F9 and Excel will convert this to something like
=WORKDAY(A1,1,{37987,37999,38000})
 
Duh! Of course. I don't know what I was thinking. Yes, that works just
like it always has in Excel XP also. Thanks. I would select <Message>
<Cancel Message>, but I'm sure it's too late. :>0

"... select A4:A6 within the Workday function, and then press F9."

Dana
 
Back
Top