This example shows you the functions that can be used to calculate the number of days between two input dates:

`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.