workdays formula

Posted by Barry on December 21, 2001 1:33 PM

I would like to take a cell with a date and add a cell with a number and put the results in another cell minus weekends. Do you have any examples of the Workdays formula that will do this.
Thanks

Posted by IML on December 21, 2001 1:42 PM

Assuming a date in A1 and days in A2, you could use
=(A1+A2)+(WEEKDAY(A1+A2)=7)*2+(WEEKDAY(A1+A2)=1)
and format as date.

You can also install the analysis toolpak and take advance of the
=Workday function
See help for full description, but this lets you exclude defined holidays as well.

Posted by Barry on December 21, 2001 2:12 PM

This formulA is adding the weekends into the results.

Here is an example of what I'm trying to do.
A1 = 12/19/01, B1 = 30, C1= A1 + B1 excluding weekends so the results should = 01/30/02.
Thanks

Posted by IML on December 21, 2001 2:23 PM

You're right. That formula just makes sure you don't land on a weekend. Install the anaylis toolpak. Your format would be
=WORKDAY(DATEVALUE("12/19/01"),30)
or
=WORKDAY(a1,b1)using the cells.

Posted by Aladin Akyurek on December 21, 2001 2:25 PM

As IML suggested, you can also use

=WORKDAY(A1,B1)

If you don't have the WORKDAY function available, activate Tools|Add-Ins and check Analysis Toolpak.

Aladin