Count weekdays & weekends between two dates

Forums Dynamic Forms for SharePoint Count weekdays & weekends between two dates

This topic contains 3 replies, has 2 voices, and was last updated by  Alexander Bautz 1 week, 2 days ago.

  • Author
    Posts
  • #25638

    Maciek Grischke
    Participant

    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.

  • #25640

    Alexander Bautz
    Keymaster

    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

  • #25647

    Maciek Grischke
    Participant

    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.

  • #25672

    Alexander Bautz
    Keymaster

    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

You must be logged in to reply to this topic.