Can I check if cells are a Date and change their format?

I recorded the macro and then did some changes so they would show the right date format. The problem is that depending on the report the dates are located in different columns, I would like to create a macro that I could use for all my reports, for that I would need the date formating to be applied to all cells that show a date (normally it is dd/mm/yyyy hh:ss).

This is my current code:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Freeze rows on selectedSheet
    selectedSheet.getFreezePanes().freezeRows(1);
    // Set font bold to true for range 1:1 on selectedSheet
    selectedSheet.getRange("1:1").getFormat().getFont().setBold(true);
    // Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range 1:1 on selectedSheet
    selectedSheet.getRange("1:1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
    // Indent set to 0 for range 1:1 on selectedSheet
    selectedSheet.getRange("1:1").getFormat().setIndentLevel(0);
    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("1:1"));

// HERE STARTS THE DATE FORMATTING I'D LIKE TO MAKE DYNAMIC SO IT CAN APPLY TO ALL REPORTS

    // Set format for range A:A on selectedSheet
    selectedSheet.getRange("A:A").setNumberFormatLocal("dd/mm/aaaa");
    // Set format for range U:Y on selectedSheet
    selectedSheet.getRange("U:Y").setNumberFormatLocal("dd/mm/aaaa");
    // Set format for range AA:AA on selectedSheet
    selectedSheet.getRange("AA:AA").setNumberFormatLocal("dd/mm/aaaa");
    // Auto fit the columns of all cells on selectedSheet
    selectedSheet.getRange().getFormat().autofitColumns();

}

Solution

If the script fails with your actual data, please define how to identify date columns (e.g., column names containing 'date').

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Freeze rows on selectedSheet
    selectedSheet.getFreezePanes().freezeRows(1);
    // get the header row
    let headerRow = selectedSheet.getRange("1:1")
    // get the format
    let headerFormat = headerRow.getFormat();
    // Set font bold to true for range 1:1 on selectedSheet
    headerFormat.getFont().setBold(true);
    // Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range 1:1 on selectedSheet
    headerFormat.setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
    // Indent set to 0 for range 1:1 on selectedSheet
    headerFormat.setIndentLevel(0);
    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().apply(headerRow);

    // changes
    const DATE_KEY = "Date" // modify as needed
    // get the contents in the header row
    let headerData = headerRow.getIntersection(selectedSheet.getUsedRange());
    let headerTxt = headerData.getTexts();
    // console.log(headerTxt[0].length)
    for (let c = 0; c < headerTxt[0].length; c++) {
        // check if "Date" in the header
        if (headerTxt[0][c].includes(DATE_KEY)) {
            // apply date formating
            selectedSheet.getCell(1, c).getEntireColumn().setNumberFormatLocal("dd/mm/yyyy");
        }
    }
    selectedSheet.getUsedRange().getFormat().autofitColumns();
}