Replacing dates that fall on weekends

  • Thread starter Thread starter p.numminen
  • Start date Start date
P

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?
 
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
 
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
 
Back
Top