Overview
This article outlines how to calculate an estimated completion date for a task or order in NetSuite, excluding weekends and holidays. Holidays are loaded from a CSV file stored in the File Cabinet, and the logic accounts for both hourly and daily turnaround times.
Components
1. Holiday CSV File
- Format: The CSV should have a header and list of dates in
YYYY-MM-DDformat. - Example: Date 2025-12-25 2025-01-01
2. Functions Used
getHolidayListFromCSV(fileId): Reads the holiday list from the CSV file.calculateCompletionDate(startDate, timeDetails, holidays, internalDosimeter): Calculates the completion date.isBusinessDay(date, holidays, timeZone): Checks if a date is a business day.formatDateToTimeZone(date, timeZone): Formats a date toYYYY-MM-DDin the user’s timezone.getDayInTimeZone(date, timeZone): Gets the day of the week in the user’s timezone.
Loading Holidays from CSV
function getHolidayListFromCSV(fileId) {
try {
let csvFile = file.load({ id: fileId });
let content = csvFile.getContents();
let lines = content.split(/r?n/);
let holidays = [];
for (let i = 1; i < lines.length; i++) {
let line = lines[i].trim();
if (!line) continue;
let parts = line.split(“,”);
holidays.push(parts[0].trim()); // YYYY-MM-DD
}
return holidays;
} catch (err) {
_log.error(“Error reading holiday CSV”, err);
return [];
}
}
Completion Date Calculation
function calculateCompletionDate(startDate, timeDetails, holidays, internalDosimeter) {
try {
const userTimeZone = runtime.getCurrentUser().getPreference({ name: ‘TIMEZONE’ });
const { unit, value } = timeDetails;
let completionDate = new Date(startDate);
const formattedHolidays = holidays.map(date =>
formatDateToTimeZone(new Date(date), userTimeZone)
);
const advanceToNextBusinessDay = () => {
do {
completionDate.setUTCDate(completionDate.getUTCDate() + 1);
} while (!isBusinessDay(completionDate, formattedHolidays, userTimeZone));
};
if (unit === “hours”) {
if (value === 24) {
advanceToNextBusinessDay();
} else {
completionDate.setUTCHours(completionDate.getUTCHours() + value);
}
} else if (unit === “days”) {
let daysToAdd = value;
while (daysToAdd > 0) {
advanceToNextBusinessDay();
daysToAdd–;
}
}
if (internalDosimeter && Number(internalDosimeter) > 0) {
advanceToNextBusinessDay();
}
return completionDate;
} catch (err) {
log.error(“Error @ completion date calculation”, err);
}
}
Business Day Check
function isBusinessDay(date, holidays, timeZone) {
const formatted = formatDateToTimeZone(date, timeZone);
const day = getDayInTimeZone(date, timeZone);
return !(day === 0 || day === 6 || holidays.includes(formatted));
Timezone Utilities
function formatDateToTimeZone(date, timeZone) {
return new Intl.DateTimeFormat(‘en-CA’, {
timeZone,
year: ‘numeric’,
month: ‘2-digit’,
day: ‘2-digit’
}).format(date).replace(///g, ‘-‘);
}
function getDayInTimeZone(date, timeZone) {
const parts = new Intl.DateTimeFormat(‘en-US’, {
timeZone,
weekday: ‘short’
}).formatToParts(date);
const weekdayStr = parts.find(p => p.type === ‘weekday’).value;
const map = { Sun: 0, Mon: 1, Tue: 2, Wed: 3, Thu: 4, Fri: 5, Sat: 6 };
return map[weekdayStr];
}
Example Usage
let timeDetails = { unit: “days”, value: 3 };
let holidays = getHolidayListFromCSV(HOLIDAYCSVFILE_ID);
let completionDate = calculateCompletionDate(new Date(orderAcceptedTime), timeDetails, holidays, internalDosimetry);
Notes
- Ensure the holiday CSV is properly formatted and uploaded to the File Cabinet.
- This logic assumes UTC for date manipulation but formats dates in the user’s timezone.
- Internal dosimetry adds an extra business day if applicable.