Exclude Weekends

Lori Carenzo

I am trying to take a Due Date that has multiple tasks associated with it and create a Task Due Date based off the due date using a duration, but excluding weekends.  For example, the Due Date is on a Monday, but the task needs to be completed 1 day before, which is a Sunday, so I need to date to populate to the Friday before.  Help?

 

JamesR

Lori,

Extract from the help:

WORKDAY (Date Function)

Description

Returns a date that is the specified number of working days before or after a date.

Syntax

WORKDAY(date_value, number_of_days, holiday_range)

The WORKDAY function takes the following arguments:

• date_value—The referenced cell (must be of a Date column type) or other date value.
• number_of_days—A numeric value representing the number of days before (negative number) or after (positive number) the referenced date cell.
• holiday_range (optional)—A group of cells (must be of Date column type) containing holidays to exclude.

Example

=WORKDAY([Due Date]1, 365, [Due Date]2:[Due Date]3)

Result: 3/30/15 (US date format)

=WORKDAY([Due Date]5, 6)

Result: 3/10/14 (US date format)

Notes

• By default, the WORKDAYS, NETWORKDAY, and NETWORKDAYS formulas count Saturday and Sunday as non-working days. If dependencies are enabled on your sheet you can customize the non-working days, and the formulas will use your settings in calculations.
• You can designate other dates as nonworking to exclude them when calculating the new date. To do this, enter each holiday/non-working day into a cell and then reference the range of cells in your WORKDAY formula.