› Forums › Classic DFFS › Count weekdays & weekends between two dates
- This topic has 3 replies, 2 voices, and was last updated 4 years, 5 months ago by
Alexander Bautz.
-
AuthorPosts
-
-
June 9, 2019 at 00:08 #25638
Maciek Grischke
ParticipantIs 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: 4If 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.
-
June 9, 2019 at 08:53 #25640
Alexander Bautz
KeymasterYou 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
-
June 9, 2019 at 18:32 #25647
Maciek Grischke
ParticipantI’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.
-
June 11, 2019 at 16:46 #25672
Alexander Bautz
KeymasterI 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
-
-
AuthorPosts
- You must be logged in to reply to this topic.