Issue with Date Parsing in Non-ISO Format (dd/MM/yyyy, hh:mm:ss AM/PM)

[KatieShortcuts]

Office Scripts sample scenario: Punch clock button

Problem The script was failing to correctly calculate the shift duration due to issues with how JavaScript's Date object parsed date strings in the format dd/MM/yyyy, hh:mm:ss AM/PM. This resulted in Invalid Date or NaN errors during the duration calculation.

Solution: The updated script includes a custom date parsing function that manually splits the date string into components and handles the conversion of 12-hour AM/PM times to 24-hour format. This ensures that the Clock In and Clock Out times are correctly parsed, and the duration is accurately calculated.

Updated Code:

function main(workbook: ExcelScript.Workbook) {
    let timeSheet: ExcelScript.Worksheet = workbook.getWorksheet("MyTimeSheet");
    let timeTable: ExcelScript.Table = timeSheet.getTables()[0];
    let clockInColumn: ExcelScript.TableColumn = timeTable.getColumnByName("Clock In");
    let clockOutColumn: ExcelScript.TableColumn = timeTable.getColumnByName("Clock Out");
    let durationColumn: ExcelScript.TableColumn = timeTable.getColumnByName("Duration");

    let clockInLastRow: ExcelScript.Range = clockInColumn.getRangeBetweenHeaderAndTotal().getLastRow();
    let clockOutLastRow: ExcelScript.Range = clockOutColumn.getRangeBetweenHeaderAndTotal().getLastRow();
    let date: Date = new Date();

    if (clockInLastRow.getValue() as string === "") {
        clockInLastRow.setValue(date.toLocaleString());
    } else if (clockOutLastRow.getValue() as string === "") {
        clockOutLastRow.setValue(date.toLocaleString());
        const clockInString: string = clockInLastRow.getValue() as string;
        const clockOutString: string = clockOutLastRow.getValue() as string;

        const clockInTime: Date = parseCustomDate(clockInString);
        const clockOutTime: Date = parseCustomDate(clockOutString);

        if (!isNaN(clockInTime.getTime()) && !isNaN(clockOutTime.getTime())) {
            let durationString: string = calculateDuration(clockInTime, clockOutTime);
            durationColumn.getRangeBetweenHeaderAndTotal().getLastRow().setValue(durationString);
        } else {
            durationColumn.getRangeBetweenHeaderAndTotal().getLastRow().setValue("Invalid Date");
        }
    } else {
        timeTable.addRow();
        clockInColumn.getRangeBetweenHeaderAndTotal().getLastRow().setValue(date.toLocaleString());
    }
}

function calculateDuration(startTime: Date, endTime: Date): string {
    let startHours: number = startTime.getHours();
    let startMinutes: number = startTime.getMinutes();
    let endHours: number = endTime.getHours();
    let endMinutes: number = endTime.getMinutes();

    let hourDiff: number = endHours - startHours;
    let minuteDiff: number = endMinutes - startMinutes;

    if (minuteDiff < 0) {
        minuteDiff += 60;
        hourDiff -= 1;
    }

    let durationString: string = `${hourDiff} hour${hourDiff !== 1 ? 's' : ''}`;
    if (minuteDiff > 0) {
        durationString += ` and ${minuteDiff} minute${minuteDiff !== 1 ? 's' : ''}`;
    }

    return durationString;
}

function parseCustomDate(dateString: string): Date {
    let [datePart, timePart] = dateString.split(", ");
    let [day, month, year] = datePart.split("/").map(part => parseInt(part));
    let [time, period] = timePart.split(" ");
    
    let [hours, minutes, seconds] = time.split(":").map(part => parseInt(part));

    if (period.toLowerCase() === "pm" && hours < 12) {
        hours += 12;
    } else if (period.toLowerCase() === "am" && hours === 12) {
        hours = 0;
    }

    return new Date(year, month - 1, day, hours, minutes, seconds);
}

[AlexJerabek]

Hi @KatieShortcuts,

Thank you for reporting this issue and proposing a fix. @alison-mk, could you please verify this solution and update the sample?

[alison-mk]

Hi @KatieShortcuts, thank you for this very thoughtful and thorough submission! I'm reviewing your suggestion and will report back here once I have more information.