Set a date as a constant to work back from to show only weekdays
 

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 "round" 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.

Set a date as a constant to work back from to show only weekdays
 

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: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=499256

Set a date as a constant to work back from to show only weekdays
 

On Sun, 8 Jan 2006 21:46:27 -0600, Vito
wrote:


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.

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

Set a date as a constant to work back from to show only weekda
 

"Ron Rosenfeld" wrote:

On Sun, 8 Jan 2006 21:46:27 -0600, Vito
wrote:


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.

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


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.