Replacing dates that fall on weekends

[p.numminen]

In Microsoft Excel 2003, how can I automatically determine Saturdays
and Sundays in every cell that has a date in it in a selection range?
Is there a way to change Saturdays and Sundays to the preceding
Friday?

How about if I would like to define some specific dates besides
weekends that should be replaced by the nearest preceding acceptable
date?

 

[Pete_UK]

Depending on what you are trying to do, you could use the NETWORKDAYS
function (part of the ATP add-in), which allows you to omit weekends
and also holidays (you supply a list).

A simple way of generating sequential dates without including weekends
is to put your start-date in A1 (for example), and this should be a
weekday date. Then you can enter this formula in the next cell:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

Ensure that the cell is formatted as a date, then copy this formula
across or down as required.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

If Pete's response does not give you what you need (although I think it
should), then perhaps this will. It takes a date in A1 and either leaves it
alone if it is a weekday date or changes the date to the Friday before if it
is a weekend date...

=A1-(WEEKDAY(A1,2)-5)*(WEEKDAY(A1,2)>5)

Rick