**On January 27, 2021, Google is changing the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.**

**Contents:**

Calculates the work date that is before or after a start date, as specified by a number of days. You can also specify which days of the week are working days and a list of holidays via parameters.

- Input can be a column reference or the output of the DATE or TIME function.
- See DATE Function.
- See TIME Function.

- The first value is used as the baseline.
- The second value is the number of days before or after the start date.
- If the second value is negative, the function returns the number of days before the start date.

**Wrangle vs. SQL:** This function is part of Wrangle
, a proprietary data transformation language. Wrangle
is not SQL. For more information, see Wrangle Language.

## Basic Usage

workdayintl(StartDate, 5,'0000001')

**Output:** Returns the working date that is five days after the `StartDate`

, assuming that every day except for Sunday is a working day.

## Syntax and Arguments

workdayintl(date1,numDays[,str_workingdays][,array_holiday])

Argument | Required? | Data Type | Description |
---|---|---|---|

date1 | Y | datetime | Starting date to compare |

numDays | Y | integer | Number of days before or after starting date |

str_workingdays | N | string | Seven-character string identifying the weekend days. |

array_holiday | N | array | An array of string values representing the valid dates of holidays. |

For more information on syntax standards, see Language Documentation Syntax Notes.

### date1

Date value can be a column reference or output of the `DATE`

function or the `TIME`

function.

- For more information, see DATE Function.
- For more information, see TIME Function.

**Usage Notes:**

Required? | Data Type | Example Value |
---|---|---|

Yes | Datetime (Column reference or date output of DATE or TIME function) | `LastContactDate` |

### numDays

An Integer that defines the number of working days distance from the start date. The function returns the start date plus or minus the number of working days represented in this Integer.

If the integer is less than zero, the number of working days are counted backward from the start date.

**Usage Notes:**

Required? | Data Type | Example Value |
---|---|---|

Yes | integer | 10 |

### str_workingdays

A seven-character string identifying the days of the week that are working days.

- String value must be seven characters long and contain only
`0`

or`1`

characters. All other values are ignored. - First character in the string represents Monday and last character in the string represents Sunday.
- If the string is not specified, then a Monday - Friday workweek is used.

Examples:

str_workingdays | Weekend days |
---|---|

`'0000011'` | Saturday and Sunday (default) |

`'1000011'` | Monday, Saturday, and Sunday |

`'0000000'` | None. |

**Usage Notes:**

Required? | Data Type | Example Value |
---|---|---|

Yes | Array | ['1000011'] |

### array_holiday

An array containing the list of holidays, which are factored in the calculation of working days.

Values in the array must be in either of the following formats:

['2020-12-24','2020-12-25'] ['2020/12/24','2020/12/25']

**Usage Notes:**

Required? | Data Type | Example Value |
---|---|---|

Yes | Array | ['2018-12-24','2018-12-25','2018-12-31','2019-01-01'] |

## Examples

**Tip:** For additional examples, see Common Tasks.

### Example - Date diffing functions

`DATEDIF`

- Calculates difference between two input dates for a specified unit of measure. In this example, the unit of measure is day. See DATEDIF Function.`NETWORKDAYS`

- Calculates number of working days between two input dates, assuming a Monday - Friday workweek. See NETWORKDAYS Function.`NETWORKDAYSINTL`

- Calculates number of working days between two input dates with optional specified workweek. see NETWORKDAYSINTL Function.`WORKDAY`

- Calculates the date of a working day that is a specified number of working days before or after a specified date. See WORKDAY Function.`WORKDAYINTL`

- Calculates the date of a working day that is a specified number of working days before or after a specified date, factoring in an optional set of workday schedule for the week. See WORKDAYINTL Function.

**Source:**

The following dataset contains two columns of dates.

- The first column values are constant. This date falls on a Monday.

Date1 | Date2 |
---|---|

2020-03-09 | 2020-03-13 |

2020-03-09 | 2020-03-06 |

2020-03-09 | 2020-03-16 |

2020-03-09 | 2020-03-23 |

2020-03-09 | 2020-04-10 |

2020-03-09 | 2021-03-10 |

**Transformation:**

The first transformation calculates the number of raw days between the two dates:

Transformation Name | `New formula` |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `datedif(Date1, Date2, day)` |

Parameter: New column name | `'datedif'` |

This step computes the number of working days between the two dates. Assumptions:

- Workweek is Monday - Friday.
- There are no holidays.

Transformation Name | `New formula` |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `networkdays(Date1, Date2, [])` |

Parameter: New column name | `'networkDays'` |

For some, March 17 is an important date, especially if you are Irish. To add St. Patrick's Day to the list of holidays, you could add the following transformation:

Transformation Name | `New formula` |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `networkdays(Date1, Date2, ['2020-03-17'])` |

Parameter: New column name | `'networkDaysStPatricks'` |

In the following transformation, the NETWORKDAYSINTL function is applied so that you can specify the working days in the week:

Transformation Name | `New formula` |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `networkdaysintl(Date1, Date2, '1000011', [])` |

Parameter: New column name | `'networkDaysIntl'` |

The following two functions enable you to calculate a specific working date based on an input date and integer number of days before or after it. In the following, the date that is five working days before the `Date2`

column is computed:

Transformation Name | `New formula` |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `workday(Date2, -5)` |

Parameter: New column name | `'workday'` |

Suppose you wish to factor in a four-day workweek, in which Friday through Sunday is considered the weekend:

Transformation Name | `New formula` |
---|---|

Parameter: Formula type | `Single row formula` |

Parameter: Formula | `workdayintl(Date2, -5,'0000111')` |

Parameter: New column name | `'workdayintl'` |

**Results:**

Date1 | Date2 | workdayintl | workday | networkDaysIntl | networkDaysStPatricks | networkDays | datedif |
---|---|---|---|---|---|---|---|

2020-03-09 | 2020-03-13 | 2020-03-05 | 2020-03-06 | 4 | 5 | 5 | 4 |

2020-03-09 | 2020-03-06 | 2020-02-27 | 2020-02-28 | null | null | null | -3 |

2020-03-09 | 2020-03-16 | 2020-03-15 | 2020-03-09 | 4 | 6 | 6 | 7 |

2020-03-09 | 2020-03-23 | 2020-03-12 | 2020-03-16 | 8 | 10 | 11 | 14 |

2020-03-09 | 2020-04-10 | 2020-04-02 | 2020-04-03 | 20 | 24 | 25 | 32 |

2020-03-09 | 2021-03-10 | 2021-03-02 | 2021-03-03 | 210 | 262 | 263 | 366 |

This page has no comments.