Count weekdays & weekends between two dates

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

Viewing 3 reply threads
  • 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

Viewing 3 reply threads
  • You must be logged in to reply to this topic.