Calculates days between 2 dates excluding weekends and holidays

ecombs · 14 November 2022 21:30

Hello everyone,

I'm trying to calculate the number of days between two date excluding weekends and holidays. I can get the normal amount of days, however it doesn't account for weekends or holidays.
I've look at just about every post relating to javascript about dates even the stevesnoderedguide

I found this snip below but it does work. Mainly because I think while loops don't work with dates.

var fromDate = 2022-05-02T05:00:00.000Z
var toDate = 2022-11-11T06:00:00.000Z
var d1 = fromDate.setDate(fromDate.getDate() + 1)

var weekendDayCount = 0;

while (fromDate < toDate) {
    fromDate.setDate(fromDate.getDate() + 1);
    if (fromDate.getDay() === 0 || fromDate.getDay() == 6) {
        weekendDayCount++;
        return weekendDayCount
    }
}

any help would be greatly appreiated.

E1cid · 14 November 2022 22:19

Your iso timestamps are not strings.
The code does not create date objects for setDate to work on.
try this to return the number of weekdays.

msg.fromDate = "2022-05-02T05:00:00.000Z";
msg.toDate = "2022-11-11T06:00:00.000Z";
let startDate = new Date(msg.fromDate);
let finishDate = new Date(msg.toDate);

let weekDayCount = 0;
while (startDate < finishDate) {
    if (startDate.getDay() > 0 && startDate.getDay() < 6) {
        weekDayCount++;    
    }
    startDate.setDate(startDate.getDate() + 1);
}
msg.payload = weekDayCount;
return msg;

ecombs · 15 November 2022 15:33

@E1cid
Awesome Thanks works perfectly wasn't aware of setDate not creating an object. How would I be able to take off holidays as well?

I assume I would have to convert it back into a date and check if it matches

E1cid · 15 November 2022 15:49

That's a bit more tricky. Off the top of my head you would have to create an array of the holidays (there are api's for countries holidays google may help and there https://api.generadordni.es/v2/holidays/holidays?country=GB&year=2022).
Then you could check if StartDate is in holiday array, or loop through holiday array and check they are between fromDate and endDate, and are weekdays, if so increment a holiday count. You can then take holiday_count from weekday_count.

Have a go, if you have issues post back where you have gotten to, and I am sure the forum peeps will help.

ecombs · 15 November 2022 17:13

@E1cid
I was thinking about doing something like this but its skipping my for loop for some reason.
This would make it easier to check if the date falls on a thursday,friday or saturday during the initial check as well.

msg.fromDate = "2022-11-01T05:00:00.000Z";
msg.toDate = "2022-11-30T06:00:00.000Z";
let startDate = new Date(msg.fromDate);
let finishDate = new Date(msg.toDate);

var Holiday_month = [6,11,12]
var Holiday_day = [4,24,25]
var positionsinArray = 3

let weekDayCount = 0;
while (startDate < finishDate) {
    for (var i = 0; i <= 3; i++) {
        if (startDate.getMonth() == Holiday_month[i] && startDate.getDate() == Holiday_day[i]) {
            
            weekDayCount--
        }
    }
    if (startDate.getDay() > 0 && startDate.getDay() < 6) {
        weekDayCount++;
    } 
      
    startDate.setDate(startDate.getDate() + 1);
}
msg.payload = {
    "weekDayCount":weekDayCount,
    "holiday_rule": Holiday_rule
    };
return msg;

E1cid · 15 November 2022 17:49

Holidays here in UK shift, not sure of US. I think I would create an array of holiday iso timestamps, the loop through to create count of holidays that are weekdays and between the startDate and finishDate.
e.g.

[{"id":"9aacb749e03a522c","type":"inject","z":"452103ea51141731","name":"","props":[{"p":"fromDate","v":"2022-05-02T00:00:00.000Z","vt":"str"},{"p":"toDate","v":"2022-11-11T23:59:00.000Z","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":3420,"wires":[["403db4b1082bddf0"]]},{"id":"403db4b1082bddf0","type":"change","z":"452103ea51141731","name":"","rules":[{"t":"set","p":"url","pt":"msg","to":"\"https://api.generadordni.es/v2/holidays/holidays?country=US&year=\" & $moment().format(\"YYYY\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":3420,"wires":[["56d0f4ca24930802"]]},{"id":"56d0f4ca24930802","type":"http request","z":"452103ea51141731","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":470,"y":3420,"wires":[["a743bdc005103478","308ef04085e71f3b"]]},{"id":"a743bdc005103478","type":"change","z":"452103ea51141731","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[$$.payload.start]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":3420,"wires":[["88773e494c57f6ac","308ef04085e71f3b"]]},{"id":"308ef04085e71f3b","type":"debug","z":"452103ea51141731","name":"debug 108","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":3380,"wires":[]},{"id":"88773e494c57f6ac","type":"function","z":"452103ea51141731","name":"function 17","func":"let startDate = new Date(msg.fromDate);\nlet finishDate = new Date(msg.toDate);\nlet count_hols = msg.payload.reduce((acc, str) => {\n    let date = new Date(str);\n    let day = date.getDay();\n    if(day > 0 && day < 6 && date >= startDate && date <= finishDate){\n        acc++;\n    }\n    return acc;\n},0)\n\nlet weekDayCount = 0;\nwhile (startDate < finishDate) {\n    if (startDate.getDay() > 0 && startDate.getDay() < 6) {\n        weekDayCount++;    \n    }\n    startDate.setDate(startDate.getDate() + 1);\n}\nmsg.payload = weekDayCount - count_hols;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":230,"y":3480,"wires":[["1ed7b575b8922ee8"]]},{"id":"1ed7b575b8922ee8","type":"debug","z":"452103ea51141731","name":"debug 107","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":3600,"wires":[]}]

You could also add 2023 to the array to allow for cross year periods.

//create start and finish time objectst starte objects
let startDate = new Date(msg.fromDate);
let finishDate = new Date(msg.toDate);

//loop through holiday iso strings to check if between date and a weekday
let count_hols = msg.payload.reduce((acc, str) => {
    let date = new Date(str);
    let day = date.getDay();
    if(day > 0 && day < 6 && date >= startDate && date <= finishDate){
        acc++;
    }
    return acc;
},0)
// lopp through start and finish time period and check for weekday
let weekDayCount = 0;
while (startDate < finishDate) {
    if (startDate.getDay() > 0 && startDate.getDay() < 6) {
        weekDayCount++;    
    }
    startDate.setDate(startDate.getDate() + 1);
}
//subtract hols from weekdays
msg.payload = weekDayCount - count_hols;
return msg;

The api call for holidays could be run once a year on Jan 1st to update a context var of iso holiday timestamps.

ecombs · 15 November 2022 22:26

@E1cid
In the us the holidays are on the same date every year so an array of month and day is the easiest thing to do. That way it calculates it only when processing the form.

msg.fromDate = "2022-11-01T05:00:00.000Z";
msg.toDate = "2022-11-30T06:00:00.000Z";
let startDate = new Date(msg.fromDate);
let finishDate = new Date(msg.toDate);

var Holiday_month = [0,5,10,11]
var Holiday_day = [0,3,23,24]
var positionsinArray = 4

var weekDayCount = 0;
var HoliDayCount =0

var work= 0
while (startDate < finishDate) {

    if (startDate.getDay() > 0 && startDate.getDay() < 6) {
        for (var i = 0; i < positionsinArray; i++) {
            if (startDate.getMonth() == Holiday_month[i] && startDate.getDate() == Holiday_day[i]) {
                HoliDayCount++
                if (startDate.getDay()== 4)//if its a thursday, friday is off aswell
                    
                    node.warn('StartDate='+startDate.getDay())
                HoliDayCount++
                
            }
        }

        weekDayCount++
    }

    startDate.setDate(startDate.getDate() + 1)
    
}

msg.payload = {
    "weekDayCount": weekDayCount,
    "HoliDayCount": HoliDayCount,
    "ActualWorkDays": weekDayCount - HoliDayCount
}
return msg;

Thank you for your help with dealing with dates. it has helped me a lot.

TotallyInformation · 16 November 2022 19:13

Please use a suitable library for this, don't attempt to create your own. There are lots of edge-cases when calculating dates such as leap years and while some public holidays might usually happen on a specific date, that isn't always the case. Some may get occasionally moved for example. It gets worse if you also need to calculate time and date.

ecombs · 16 November 2022 19:29

@TotallyInformation

This is not a library; this is for a function node. This is not calculating date and time just date. And if I decided to make this a library for this its because a suitable library wasn't found. And "suitable" libraries at that are created by people like me, who couldn't find a suitable library in the first place. A simple solution for dates that varies would be to have a variable that grab a date then offset the holiday before the working days is calculated.

TotallyInformation · 17 November 2022 01:52

You misunderstand. I'm saying don't create a library, use an existing one. The first date calculation library I wrote was way back in the 80's. If you want to calculate between dates, there are lots of things to take into account, many of them being far from obvious. There are plenty of tried and trusted JS date libraries, just use one.

system · 1 December 2022 01:53

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Days between two dates General	6	111	8 March 2024
Add weekdays to date General	3	346	18 August 2022
Calculating days and hours between two timestamps - Almost there.. Need a tweak on this! General	6	184	6 February 2023
Days left from date to date General	4	1081	24 March 2021
Change a string type to Date type General	10	4557	21 May 2021

Calculates days between 2 dates excluding weekends and holidays

Related Topics