WORKDAY.INTL function

PRO BASIC

The WORKDAY.INTL function in Jspreadsheet Formulas Pro is a useful tool to calculate a date based on a specified number of workdays away from a starting date, excluding weekends and holidays. If you provide a starting date and the number of workdays, this function will return the corresponding date after taking into account only weekdays and skipping any holidays. This function is particularly helpful for project management or scheduling tasks, allowing you to easily determine deadlines or project completion dates.

Documentation

Returns the date that is the indicated number of working days before or after a date (the starting date), excluding weekends and holidays.

Syntax

WORKDAY.INTL(start_date, days, [weekend], [holidays])

Parameter	Description
`start_date`	The starting date, expressed as a serial number or a date.
`days`	The number of working days before or after the start date. A positive value for days generates a future date; a negative value generates a past date.
`weekend`	Optional. A number or string representing which days of the week are weekend days and are not considered working days. The default is Saturday and Sunday ("0000011"). For example, "1111111" would mean all days are weekends. You can also specify weekend using one or more of the following: 1 = Saturday 2 = Sunday 3 = Monday 4 = Tuesday 5 = Wednesday 6 = Thursday 7 = Friday.
`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.

Behavior

The WORKDAY.INTL function calculates a specific date in future or past, skipping specified weekdays and holidays. It expects a start date, number of days and optional weekend parameters and holiday dates. Here's how it handles certain inputs:

Empty Cells: If the start date or days is an empty cell, WORKDAY.INTL will return a #VALUE! error. For the weekend or holidays parameters, an empty cell is treated as none, meaning no weekends or holidays are skipped.
Text: If the start date or days is text, WORKDAY.INTL will return a #VALUE! error. Text is allowed in the weekend or holidays parameters if it's a valid input.
Booleans: Boolean values are treated as numbers (TRUE = 1, FALSE = 0). If mistakenly used in place of dates or invalid weekend codes, they can cause unexpected results or a #VALUE! error.
Errors: If any of the parameters contain an error, WORKDAY.INTL will return that error.
Dates: Dates should be supplied as either references to cells containing dates, or as results of formulas.
Numbers: The days parameter should be a positive or negative integer. Decimal numbers are rounded down to the nearest integer.

Common Errors

Error	Description
#VALUE!	Occurs if the provided arguments are not recognized as valid dates, days or weekend parameters.
#NUM!	Occurs if the provided start date is an invalid date, the number of days plus the start date results in an invalid date, or the weekend parameter is a number less than 1 or greater than 7.

Best practices

Always ensure the start date and number of days are valid numbers or cell references, not text or boolean values.

Be careful with the weekend parameter. It can be a string of seven 0s and 1s or an integer between 1 and 7, each representing different weekend configurations. Make sure you use the right one for your needs.

If you want to skip specific holidays, provide them as a range of cells in the holidays parameter. Make sure these are valid date values.

Remember that WORKDAY.INTL automatically excludes weekends (Saturday and Sunday) unless specified differently.

Usage

A few examples using the WORKDAY.INTL function.

WORKDAY.INTL("2023-03-15", 5, "0000011")
// Returns "2023-03-22" → Adds 5 working days, skipping Saturday and Sunday.

WORKDAY.INTL("2023-03-15", -5, "0000011")
// Returns "2023-03-06" → Subtracts 5 working days, skipping weekends.

WORKDAY.INTL("2023-03-15", 10, "0000011", ["2023-03-20","2023-03-21"])
// Returns "2023-04-04" → Adds 10 working days, skips weekends and holidays on Mar 20 & 21.

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('M2Y0MDllMzNlZjRkOTk1Y2NkOTM1MWU4YmQ3MDNhNjc3NWYxMmYyZWE3MzhiNjJkNzQ2MDU4ZDRjOGEwYmQ2MDBlOTJkNDA2NDRkZDc1ZjdmZTBhMDBhMzdhNWFmYjRhODkyZTY0MWM3ODk1OGNlZjBmMDlhYWZkNTM3NjY4OTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Start Date",
        "Working Days",
        "End Date",
        "Weekend Pattern"
    ],
    [
        "2024-01-15",
        10,
        "=WORKDAY.INTL(A2,B2,D2)",
        "0000011"
    ],
    [
        "2024-02-01",
        -5,
        "=WORKDAY.INTL(A3,B3,D3)",
        "0000011"
    ],
    [
        "2024-03-01",
        7,
        "=WORKDAY.INTL(A4,B4,D4)",
        "0000011"
    ],
    [
        "2024-04-15",
        15,
        "=WORKDAY.INTL(A5,B5,D5)",
        "0000011"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('M2Y0MDllMzNlZjRkOTk1Y2NkOTM1MWU4YmQ3MDNhNjc3NWYxMmYyZWE3MzhiNjJkNzQ2MDU4ZDRjOGEwYmQ2MDBlOTJkNDA2NDRkZDc1ZjdmZTBhMDBhMzdhNWFmYjRhODkyZTY0MWM3ODk1OGNlZjBmMDlhYWZkNTM3NjY4OTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Start Date",
        "Working Days",
        "End Date",
        "Weekend Pattern"
    ],
    [
        "2024-01-15",
        10,
        "=WORKDAY.INTL(A2,B2,D2)",
        "0000011"
    ],
    [
        "2024-02-01",
        -5,
        "=WORKDAY.INTL(A3,B3,D3)",
        "0000011"
    ],
    [
        "2024-03-01",
        7,
        "=WORKDAY.INTL(A4,B4,D4)",
        "0000011"
    ],
    [
        "2024-04-15",
        15,
        "=WORKDAY.INTL(A5,B5,D5)",
        "0000011"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('M2Y0MDllMzNlZjRkOTk1Y2NkOTM1MWU4YmQ3MDNhNjc3NWYxMmYyZWE3MzhiNjJkNzQ2MDU4ZDRjOGEwYmQ2MDBlOTJkNDA2NDRkZDc1ZjdmZTBhMDBhMzdhNWFmYjRhODkyZTY0MWM3ODk1OGNlZjBmMDlhYWZkNTM3NjY4OTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Start Date",
        "Working Days",
        "End Date",
        "Weekend Pattern"
    ],
    [
        "2024-01-15",
        10,
        "=WORKDAY.INTL(A2,B2,D2)",
        "0000011"
    ],
    [
        "2024-02-01",
        -5,
        "=WORKDAY.INTL(A3,B3,D3)",
        "0000011"
    ],
    [
        "2024-03-01",
        7,
        "=WORKDAY.INTL(A4,B4,D4)",
        "0000011"
    ],
    [
        "2024-04-15",
        15,
        "=WORKDAY.INTL(A5,B5,D5)",
        "0000011"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('M2Y0MDllMzNlZjRkOTk1Y2NkOTM1MWU4YmQ3MDNhNjc3NWYxMmYyZWE3MzhiNjJkNzQ2MDU4ZDRjOGEwYmQ2MDBlOTJkNDA2NDRkZDc1ZjdmZTBhMDBhMzdhNWFmYjRhODkyZTY0MWM3ODk1OGNlZjBmMDlhYWZkNTM3NjY4OTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Start Date",
        "Working Days",
        "End Date",
        "Weekend Pattern"
    ],
    [
        "2024-01-15",
        10,
        "=WORKDAY.INTL(A2,B2,D2)",
        "0000011"
    ],
    [
        "2024-02-01",
        -5,
        "=WORKDAY.INTL(A3,B3,D3)",
        "0000011"
    ],
    [
        "2024-03-01",
        7,
        "=WORKDAY.INTL(A4,B4,D4)",
        "0000011"
    ],
    [
        "2024-04-15",
        15,
        "=WORKDAY.INTL(A5,B5,D5)",
        "0000011"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub