Let's say that you must deliver on a weekday but the date shown in A1 is on a Saturday or Sunday.

Use this formula to get the preceding Friday (if A1 is already a weekday, it will just show the date in A1)

=MIN(A1-(WEEKDAY(A1,2)-5),A1)

Use this formula to get the following Monday (if A1 is already a weekday, it will just show the date in A1)

=IF(WEEKDAY(A1,2)>5,A1+(8-WEEKDAY(A1,2)),A1)

To just avoid Sundays, use this formula for the preceding Saturday.

=MIN(A1-(WEEKDAY(A1,2)-6),A1)

Or this formula to get the following Monday

=IF(WEEKDAY(A1,2)>6,A1+(8-WEEKDAY(A1,2)),A1)

© Copyright
andrewsexceltips.net All Rights Reserved.