Count weekdays & weekends between two dates

[Maciek Grischke]

Is there an easy way to count weekdays and weekends between two dates?

I found a few JS examples online, but some of them are just too big and don’t work as they should. Not when I tried them anyway.

If there is not an easy way with DFFS and standard JS, I am already using Moment.js library on my site. Using Moment.js is so much easier when working with dates.

I want the user to enter two dates: Start Date and End Date and to set two separate fields Weekdays and Weekends automatically.

For example:

Start Date: 01/06/2019
End Date: 09/06/2019
Weekdays: 5
Weekends: 4

If it’s easier to use Moment.js, is it possible to “embed” Moment.js library within DFFS?

I’m thinking about WEEKDAY() function in Excel which simply returns a Day Number. Then COUNTIF() function to count days that are less than 6 and days that are more than 5.

For some reason, I find it easier with Moment.js library.

We want to minimise human error.

[Alexander Bautz]

You can load moment.js by adding the path the the file in the “Load these files before executing the Custom JS” above the Custom JS textarea like this:

https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment.js

Please note that it does not work loading the minified version.

Then wrap the code where you want to use moment like this in your Custom JS:

require(["moment"], function(moment){
    // add the code using moment here - the below line is just a test
    console.log(moment().format('MMMM Do YYYY, h:mm:ss a'));
});

Alexander

[Maciek Grischke]

I’ve managed to do it without Moment.js and here’s how:

//Count weekdays and weekends
 function onCallDays() {
    var onCallStartDate = getFieldValue("OnCallStart");
    var onCallEndDate = getFieldValue("OnCallEnd");
// the following 4 lines convert UK date format MM/DD/YYYY (my sharepoint region) to US date format DD/MM/YYYY needed for JS to work in my case
    var startDate2 = onCallStartDate.split("/");
    var endDate2 = onCallEndDate.split("/");
    var startDate3 = startDate2[1] + "/" + startDate2[0] + "/" +startDate2[2];
    var endDate3 = endDate2[1] + "/" + endDate2[0] + "/" +endDate2[2];
// the next two lines are first and second date
    const date1 = new Date(startDate3);
    const date2 = new Date(endDate3);
// this line retrieves a day number ie Sunday is 0, Saturday is 6, this line is not required, I used it for console.log
    var dateN = date1.getDay();
// the next two line count the difference in days between two dates
    const diffTime = Math.abs(date2.getTime() - date1.getTime());
    const daysDiff = Math.ceil(diffTime / (1000 * 60 * 60 * 24))+0;
    var weekDays = 0;
    var weekEnds = 0;
    var dateTn = 0;
// this is where counting begins
    for (var i=0; i<=daysDiff; i++) {
      var dateT = date1.setDate(date1.getDate() + dateTn);
      var dateR = new Date(dateT);
      var dayN = dateR.getDay();
      if (dayN>0 && dayN<6) {
      weekDays++;
    }
    if (dayN===0 || dayN==6) {
       weekEnds++;
    }
    dateTn = 1;
 }
setFieldValue("OnCall",weekDays);
setFieldValue("OnCallWknd",weekEnds);
}

Alexander, it works, but if you think there’s a more efficient way to do this, please correct me.

The examples I’ve seen online were much bigger, but perhaps they included “holidays”, which I didn’t need.

[Alexander Bautz]

I don’t see any problems with the way you have solved it so if it does what you need, it’s a job well done!

Best regards,
Alexander

[Author]

Posts