Issue with Date Parsing in Non-ISO Format (dd/MM/yyyy, hh:mm:ss AM/PM) #770
Description
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);
}