ExcelScript.DateTimeDataValidation interface

Package:

ExcelScript

Represents the date data validation criteria.

Remarks

Examples

/**
 * This script sets a validation rule that only allows for certain dates to be entered.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the range D2:D6 in the current worksheet.
    const selectedSheet = workbook.getActiveWorksheet();
    const range = selectedSheet.getRange("D2:D6");

    // Set a validation rule to only allow values that are dates in the year 2023.
    let dataValidation = range.getDataValidation();
    const dateValidationRule: ExcelScript.DateTimeDataValidation = {
        formula1: "1/1/2023",
        formula2: "12/31/2023",
        operator: ExcelScript.DataValidationOperator.between
    };
    dataValidation.setRule({ date: dateValidationRule});

    // Set an appropriate error message.
    dataValidation.setErrorAlert({ 
        showAlert: true, 
        title: "2023 date required", 
        message: "Please enter a date in the year 2023.", 
        style: ExcelScript.DataValidationAlertStyle.stop
    });
}

Properties

formula1	Specifies the right-hand operand when the operator property is set to a binary operator such as GreaterThan (the left-hand operand is the value the user tries to enter in the cell). With the ternary operators Between and NotBetween, specifies the lower bound operand. When setting the value, it can be passed in as a Date, a Range object, or a string formula (where the string is either a stringified date/time in ISO8601 format, a cell reference like "=A1", or a formula like "=MIN(A1, B1)"). When retrieving the value, it will always be returned as a string formula, for example: "=10", "=A1", "=SUM(A1:B5)", etc.
formula2	With the ternary operators Between and NotBetween, specifies the upper bound operand. Is not used with the binary operators, such as GreaterThan. When setting the value, it can be passed in as a Date, a Range object, or a string (where the string is either a stringified date/time in ISO8601 format, a cell reference like "=A1", or a formula like "=MIN(A1, B1)"). When retrieving the value, it will always be returned as a string formula, for example: "=10", "=A1", "=SUM(A1:B5)", etc.
operator	The operator to use for validating the data.

Property Details

formula1

Specifies the right-hand operand when the operator property is set to a binary operator such as GreaterThan (the left-hand operand is the value the user tries to enter in the cell). With the ternary operators Between and NotBetween, specifies the lower bound operand. When setting the value, it can be passed in as a Date, a Range object, or a string formula (where the string is either a stringified date/time in ISO8601 format, a cell reference like "=A1", or a formula like "=MIN(A1, B1)"). When retrieving the value, it will always be returned as a string formula, for example: "=10", "=A1", "=SUM(A1:B5)", etc.

formula1: string | Date | Range;

Property Value

string | Date | ExcelScript.Range

formula2

With the ternary operators Between and NotBetween, specifies the upper bound operand. Is not used with the binary operators, such as GreaterThan. When setting the value, it can be passed in as a Date, a Range object, or a string (where the string is either a stringified date/time in ISO8601 format, a cell reference like "=A1", or a formula like "=MIN(A1, B1)"). When retrieving the value, it will always be returned as a string formula, for example: "=10", "=A1", "=SUM(A1:B5)", etc.

formula2?: string | Date | Range;

Property Value

string | Date | ExcelScript.Range

operator

The operator to use for validating the data.

operator: DataValidationOperator;

Property Value

ExcelScript.DataValidationOperator