Formula to exclude weekends

I am trying to setup a sheet for due dates and completions. Is there a date formula that I could copy down the column so that it would exclude weekends?

there's a little known formula that is in excel that can test if a date falls on a certain day.

days(startdate,enddate,daynumber)
will return a count of the number of occurences of daynumber in the date range

for daynumber:
1=sunday
2=monday
...
7=saturday

so if you added a column and added the line:
=IF(OR(days(B2,B2,7),days(B2,B2,1)),"Weekend","Weekday")
It would test if the date in cell b2 was a weekend or a weekday. You could manipulate this for your purposes quite easily

Originally Posted by MDubbelboer

there's a little known formula that is in excel that can test if a date falls on a certain day.

days(startdate,enddate,daynumber)
will return a count of the number of occurences of daynumber in the date range

for daynumber:
1=sunday
2=monday
...
7=saturday

so if you added a column and added the line:
=IF(OR(days(B2,B2,7),days(B2,B2,1)),"Weekend","Weekday")
It would test if the date in cell b2 was a weekend or a weekday. You could manipulate this for your purposes quite easily

Another alternative is to install the Analysis Toolpack (Tools -> Addins).

Then there is the function WORKDAY(). This will probably work as well. I'm not 100% sure what happens if you distribute a worksheet with this function if they do not have the Analysis Tookpack installed. (Someone else will have to provide that information.

The basic operation is to go =WORKDAY([Start_date], [Days], [Holidays]) and it will give you the workday that is [Days] after the [Start_date] (excluding weekends/holidays).

Scott