The NETWORKDAYS formula in Excel is a powerful tool for calculating the number of working days between two dates. It excludes weekends and accounts for specified holidays, making it ideal for tracking time-off periods, project timelines, or employee attendance.
This article guides you through creating a JavaScript-based workaround for the NETWORKDAYS formula within an app, ensuring the functionality works seamlessly without relying on Excel formulas.
Setting Up Your Data
Create the Required Columns:
dayOffStart: A date column for the start of the time-off period.
dayOffEnd: A date column for the end of the time-off period.
Holidays: A number column for specifying the number of holiday days (e.g., weekends or public holidays) that should be excluded.
Input Your Data:
Enter the start date in dayOffStart.
Enter the end date in dayOffEnd.
Add the total number of holidays to the Holidays column.
JavaScript Code
To calculate working days, use the following JavaScript code in your app's JavaScript column:
function networkDays(p1, p2, holidaysCount = 0) {
// Convert p1 and p2 to Date objects
const start = new Date(p1);
const end = new Date(p2);
// Ensure the start date is before the end date
if (start > end) {
[start, end] = [end, start];
}
let count = 0;
// Loop through each day between start and end
for (let current = new Date(start); current <= end; current.setDate(current.getDate() + 1)) {
// Check if the current day is a weekday (Mon-Fri) and not a weekend
if (current.getDay() !== 0 && current.getDay() !== 6) {
count++;
}
}
// Subtract holidays count from total weekdays
count -= holidaysCount;
// Ensure the result is not negative
return Math.max(count, 0);
}
const result = networkDays(p1, p2, p3);
return result;How the Code Works
p1 (dayOffStart): Represents the start date of the range.
p2 (dayOffEnd): Represents the end date of the range.
p3 (Holidays): The total number of non-working holiday days to exclude.
The code counts weekdays (Monday to Friday) between the start and end dates and subtracts the number of holidays specified. It ensures no negative values, even if holidays exceed total weekdays.
Step-by-Step Guide
Navigate to the Timeoff Request page:
Locate the fields for dayOffStart, dayOffEnd, and Holidays on the homepage.
Enter Your Data:
Input the start date, end date, and number of holidays in the respective fields.
View Results:
The app will calculate the number of working days based on your inputs, excluding holidays, and display the result in a designated column.
This is the example of the app, it's a template and you can copy the template to your team so you will be able to analyze the functionality by yourself.