Date Calculation

1

Hi, currently I create a app with date count. In the Formula it only able to count total days based on Monday to Friday excluded weekend, unfortunately this formula cannot count excluded holiday. Anyone know how to add on the formula or code which can help me count the total days excluded weekend and holiday?

image
image834×274 6.84 KB

image
image2010×1868 398 KB

Thanks
Alex

2

Hello @Alex_Hoo

This can be quite complex.
In this example, I'm managing US holidays within the script, so they may need to be manually added or adjusted. However, I suggest using a library to handle this task. Additionally, I'm using moment.js in this example, so ensure that it's included.

(function() {
    'use strict';

    // Calculate the nth weekday of a given month and year
    function getNthWeekdayOfMonth(year, month, weekday, nth) {
        var date = moment([year, month]);
        if (nth > 0) {
            date.startOf('month');
            while (date.day() !== weekday) {
                date.add(1, 'days');
            }
            date.add((nth - 1) * 7, 'days');
        } else {
            date.endOf('month');
            while (date.day() !== weekday) {
                date.subtract(1, 'days');
            }
            date.add((nth + 1) * 7, 'days');
        }
        return date;
    }

    // Initialize holiday dates for the specified year
    function initializeHolidaysForYear(year) {
        var holidays = {
            '01-01': 'New Year\'s Day',    // New Year's Day
            '07-04': 'Independence Day',   // Independence Day
            '12-25': 'Christmas Day'       // Christmas Day
        };

        // Add dynamically calculated holidays
        holidays[getNthWeekdayOfMonth(year, 4, 1, -1).format('MM-DD')] = 'Memorial Day';         // Last Monday of May
        holidays[getNthWeekdayOfMonth(year, 8, 1, 1).format('MM-DD')] = 'Labor Day';            // First Monday of September
        holidays[getNthWeekdayOfMonth(year, 10, 4, 4).format('MM-DD')] = 'Thanksgiving Day';    // Fourth Thursday of November

        return holidays;
    }

    // Check if a specific date is a holiday
    function isHoliday(date) {
        var yearHolidays = initializeHolidaysForYear(date.year());
        var dateStr = date.format('MM-DD');
        return yearHolidays.hasOwnProperty(dateStr);
    }

    // Calculate working days excluding weekends and holidays
    function calculateWorkingDays(startDate, endDate) {
        var start = moment(startDate);
        var end = moment(endDate);
        var workingDays = 0;

        while (start.isSameOrBefore(end)) {
            // Exclude weekends and holidays
            if (start.day() !== 0 && start.day() !== 6 && !isHoliday(start)) {
                workingDays++;
            }
            start.add(1, 'days');
        }

        return workingDays;
    }

    // Event handler to update the total working days
    function updateWorkingDays(event) {
        var record = event.record;
        var startDate = moment(record['StartDate'].value);
        var endDate = moment(record['EndDate'].value);

        if (startDate.isValid() && endDate.isValid() && startDate.isBefore(endDate)) {
            record['TotalDay'].value = calculateWorkingDays(startDate, endDate);
        } else {
            record['TotalDay'].value = 0;
        }

        // Disable the TotalDay field to prevent editing
        record['TotalDay'].disabled = true;

        return event;
    }

    // Register event handlers for changes in StartDate and EndDate
    kintone.events.on([
        'app.record.edit.change.StartDate',
        'app.record.edit.change.EndDate',
        'app.record.create.change.StartDate',
        'app.record.create.change.EndDate',
        'app.record.detail.show'
    ], updateWorkingDays);
})();

Field Info:

  • Start Date (Date field) - Field Code: StartDate
  • End Date (Date field) - Field Code: EndDate
  • Total Days (Number field) - Field Code: TotalDay

You can find moment.js here: https://momentjs.com/

1 Like
3

Hi Chris,

Thanks and appreciated your help, I got 2 questions.

  1. You mentioned that you managing US holidays within the script. Mean in this script I only modify the holiday date and details only right?

image
image3347×678 78.8 KB

  1. You suggest using the library to handle the task which you suggest to use moment.js. Where I should add on the library in Kintone.
    image

Thanks & Regards
Alex

4

Hello @Alex_Hoo

  1. Correct

  2. Handling holidays differs from the functionality provided by moment.js. Moment.js primarily serves to parse, validate, manipulate, and display dates and times in JavaScript. I'm not an expert in this area, so I'm not quite sure what people use in this kind of stuff, but the following seems to be a popular library: