Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

=Workday function

Posted by John on December 14, 2001 11:34 AM
How can I modify the =workday(today(),1) formula, to go down a column and sequence the dates so that the cells show each working day for say, 1 year beyond today? (it's probably something incredibly simple, but I'm stumped)

John Unger


Check out our Excel Resources

Re: =Workday function

Posted by IML on December 14, 2001 12:24 PM

John,
assuming your first formula is in A1, you could use
=WORKDAY(A1,1,holiday) in cell B2 and copy it down.

I have another workday question. this may come into play to with your situation using now.

Let say you want workday to return today if today is a workday, or the next workday if today is a weekend or holiday.
I think
=WORKDAY(a1,1*OR(WEEKDAY(a1)={7,1}))
addresses the weekend issue, but what about holidays?

=WORKDAY(a1,1*OR(WEEKDAY(a1)={7,1},COUNTIF(holiday,a1)=1),holiday)

comes close, but doesn't account for holidays that happen to be on weekends?


Oops

Posted by IML on December 14, 2001 12:44 PM

I think the second one actually works. When I was testing I made a stupid mistake, but I think it works okay to provide
today if today is workday
next workday if today is not a workday.


Re: =Workday function

Posted by Mark W. on December 14, 2001 4:18 PM
Enter =TODAY() into cell A1. Enter =WORKDAY(A1,1)
into cell A2 and Copy down as far as you want to
go. If desired use WORDAY's option, 3rd argument
to list the holidays that your wish to exclude
(e.g., =WORKDAY(A1,1,{"12/25/01","1/1/02"}+0).


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.