As an alternate to @SudeepGhatakNZ's Power Automate solution, here is one in Office Scripts (based on this Stack Overflow answer )
function main(workbook: ExcelScript.Workbook) {
const dateString = workbook.getWorksheet("Sheet1").getRange("A1").getText();
console.log(isValidDate(dateString) ? "VALID" : "INVALID");
}
// Validates that the input string is a valid date formatted as "mm/dd/yyyy"
function isValidDate(dateString: string): boolean {
// First check for the pattern
if (!/^\d{1,2}\/\d{1,2}\/\d{4}$/.test(dateString))
return false;
// Parse the date parts to integers
var parts = dateString.split("/");
var month = parseInt(parts[0], 10);
var day = parseInt(parts[1], 10);
var year = parseInt(parts[2], 10);
// Check the ranges of month and year
if (year < 1000 || year > 3000 || month == 0 || month > 12)
return false;
var monthLength = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
// Adjust for leap years
if (year % 400 == 0 || (year % 100 != 0 && year % 4 == 0))
monthLength[1] = 29;
// Check the range of the day
return day > 0 && day <= monthLength[month - 1];
};
Please note you cannot simply rely on the built-in JavaScript Date object to do the date string parsing or validation (e.g., Date.parse(dateString)) since the behavior could vary based on the locale/culture settings of the computer. The above solution validates the date string against the explicit 'mm/dd/yyyy' format. It also checks different days of each month and leap years, etc. I feel it should be a pretty solid approach if you are looking for an Office Scripts solution.
