software

I would like to input one date as a constant and then apply a formaula in
other cells relating to this date to calculate back from but to quot;roundquot; back
to only weekdays. For example if i type in a date of February 1st 2006 (a
Wednesday) and would like to calculate backwards automatically by eleven days
it is Sat Jan 21st 2006. I would like this to round back to the nearest
previous workday which would be the Friday.


Try

=WORKDAY(A1,-11)

Where A1 contains date to count back from, e.g. Feb 1, 06

You will need the Analysis Toolpak Addin Installed.--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=499256On Sun, 8 Jan 2006 21:46:27 -0600, Vito
gt; wrote:

gt;
gt;Try
gt;
gt;=WORKDAY(A1,-11)
gt;
gt;Where A1 contains date to count back from, e.g. Feb 1, 06
gt;
gt;You will need the Analysis Toolpak Addin Installed.

The OP wanted to subtract 11 days, and then adjust for weekends. Your formula
gives a result of Tuesday, Jan 17, 2006 given his data; not the Friday, Jan 20,
2006 he specified.--ron



quot;Ron Rosenfeldquot; wrote:

gt; On Sun, 8 Jan 2006 21:46:27 -0600, Vito
gt; gt; wrote:
gt;
gt; gt;
gt; gt;Try
gt; gt;
gt; gt;=WORKDAY(A1,-11)
gt; gt;
gt; gt;Where A1 contains date to count back from, e.g. Feb 1, 06
gt; gt;
gt; gt;You will need the Analysis Toolpak Addin Installed.
gt;
gt; The OP wanted to subtract 11 days, and then adjust for weekends. Your formula
gt; gives a result of Tuesday, Jan 17, 2006 given his data; not the Friday, Jan 20,
gt; 2006 he specified.
gt;
gt;
gt; --ron
gt;

Try this

a1 Date ( 1st Feb )
a2 No Days (-11)
a3 Result (23rd Feb)
a4 formula =IF(WEEKDAY(C1)=1,C1-2,IF(WEEKDAY(C1)=7,C1-1,C1))

This checks if result is a Sun and takes 2 days of or Sat and takes one day
off, otherwise result is a workday.