Avoid weekend formula results

[SAL]

Greetings to all,

Does anyone know of a formula to that will calculate a
date that when the result falls on a weekend (Saturday or
Sunday)it will give the Friday date before. For example,
if I have January 19, 2004 entered in cell A1 and I have a
formula in cell A2 that reads =A1+5, I would like the
result to be January 23, 2004 because it is the Friday
before instead of the natural result of January 24, 2004.

Anyone know how to modify the formula for this.

Thanks,
SAL

 

[Frank Kabel]

Hi Sal

assuming your date is in A1 you can use the following
=A1-(IF(WEEKDAY(A1,2)>5,WEEKDAY(A1,2)-5))

HTH
Frank

 

[Norman Harker]

Hi SAL!

If you want to add just working days to a date use:

=WORKDAY(start_date,days,holidays)
Where:
Start_date is the first date as an acceptable date in inverted commas
(discouraged) or as a serial number or formula / function that returns
a date serial number.
Days the number of non-weekend and non-holiday days before or after
start_date. A positive value for days yields a future date; a negative
value yields a past date
Holidays is an optional range of one or more dates to exclude from the
working calendar. The list can be either a range of cells that
contains the dates or an array constant of the serial numbers that
represent the dates.

If you want to just avoid the end date being on a weekend:

=IF(WEEKDAY(A1+5)=7,A1+4,IF(WEEKDAY(A1+5)=1,A1+3,A1+5))
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[SAL]

Thanks, however how would I modify the formula in A2 to
give me the previous Friday date if the result of the of
the simple formula in my example is a weekend. In other
words, can I get the result in one step. Your formula
would work if I apply it to my simple formula in A2 (a two
step process). I would like in cell A2 to add 5 days to
the date in cell A1, but I need the result from the A2
formula to give the previous Friday date if it would
normally result in a weekend date.

Thanks again,
SAL

 

[Frank Kabel]

Hi Sal,
one way
you can replace the cell reference A1 with your formula A1+5:
=(A1+5)-(IF(WEEKDAY((A1+5),2)>5,WEEKDAY((A1+5),2)-5))

or: use Norman's suggestion
Frank