Excel – Omit weekends and/or holidays in days to complete formula

microsoft excelmicrosoft-excel-2007microsoft-excel-2010microsoft-excel-2013

Is there a way to not include weekends and or holidays in an Excel formula involving dates? Example: 

=Q2-Q1

(a date in q2 minus a date in q1 = so many days) Excluding weekends & holidays?

Best Answer

WORKDAY

The function WORKDAY is used to calculate the date a certain amount of work days before or after the start date - information here

Syntax

WORKDAY(start_date, days, [holidays])

The WORKDAY function syntax has the following arguments:

Start_date - Required. A date that represents the start date.

Days - Required. The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Holidays - Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

Example usage:

=WORKDAY(A2,A3,A4:A6) - Where A2 is your start date, A3 is the amount of days from this date you wish to add or subtract, and A4:A6 each contain a holiday date to exclude

NETWORKDAYS

Alternatively, the function NETWORKDAYS is used to calculate the amount of work days between two dates - information here

Syntax

NETWORKDAYS(start_date, end_date, [holidays])

The NETWORKDAYS function syntax has the following arguments:

Start_date - Required. A date that represents the start date.

End_date - Required. A date that represents the end date.

Holidays - Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. 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.

Example usage:

=NETWORKDAYS(A2,A3,A4:A6) - Where A2 is your start date, A3 is your end date, and A4:A6 contain holiday dates you wish to exclude.

.INTL

In case you need this to work in a situation where weekends are not defined the same way (Saturday and Sunday), both of these formulas have a .INTL function, where they also take a Weekend argument as the third argument. Note - This is only supported in Excel 2010+. Information on WORKDAY.INTL and NETWORKDAYS.INTL.

This argument would be an integer, referring to these options:

Weekend number  Weekend days
1 (default)     Saturday, Sunday
2               Sunday, Monday
3               Monday, Tuesday
4               Tuesday, Wednesday
5               Wednesday, Thursday
6               Thursday, Friday
7               Friday, Saturday
11              Sunday only
12              Monday only
13              Tuesday only
14              Wednesday only
15              Thursday only
16              Friday only
17              Saturday only

This would affect the formulas above as such, specifying Monday only as your "weekend":

=WORKDAY.INTL(A2,A3,12,A4:A6)

and

=NETWORKDAYS.INTL(A2,A3,12,A4:A6)

Related Question