Convert custom date/time field to date only

I have a csv file that brings in all dates as date/time in a "custom" format. It displays as 12/31/2021 12:00:00 AM but when I use

Part1NoBlanks.getRange("C2:C6000").setNumberFormatLocal("m/d/yyyy");

it still retains the time for any cell that has a time other than 12:00:00 AM. For example:

3/26/2020 12:00:00 AM displays as 3/26/2020 6/5/2023 11:00:00 PM still displays as 6/5/2023 11:00:00 PM

What am I missing in my script to account / address this? Thank you in advance for your help! :)

Solution

This seems like a limitation of Excel itself rather than an Office Script issue. Manually updating the format of "text date" usually causes the same problem too. Regardless, it's easy to fix with code.

btw, Using getIntersection() to narrow the data range will improve code performance.

function main(workbook: ExcelScript.Workbook) {
    // Update your Worksheet object 
    let Part1NoBlanks = workbook.getActiveWorksheet();
    let useRange = Part1NoBlanks.getUsedRange();
    let dataRange = Part1NoBlanks.getRange("C2:C6000").getIntersection(useRange);
    let dataValues = dataRange.getValues();
    dataRange.clear(ExcelScript.ClearApplyTo.contents);
    dataRange.setNumberFormatLocal("m/d/yyyy");
    dataRange.setValues(dataValues);
}