Calculating workdays
Hi all.
We are using the Vacation app to registrate employees vacation. What im looking for is a calculation of workdays for the given periode. That means number of days minus saturday and sunday. Furthermore it has to minus local Danish holidays as well.
Can anyone help ?
Flemming
-
Hi Fleming,
I assume you have a start date and an end date. Do you use 2 date fields for it (one for start and one for end) or do you use one date field with start and end? The calculation you want to do is only possible with 2 separate date fields. Than you can use:
var startDate = @yourStartDate-field var endDate = @yourEndDate-field function workingDaysBetweenDates(startDate, endDate) { // Validate input if (endDate < startDate) return 0; // Calculate days between dates var millisecondsPerDay = 86400 * 1000; // Day in milliseconds startDate.setHours(0,0,0,1); // Start just after midnight endDate.setHours(23,59,59,999); // End just before midnight var diff = endDate - startDate; // Milliseconds between datetime objects var days = Math.ceil(diff / millisecondsPerDay); // Subtract two weekend days for every week in between var weeks = Math.floor(days / 7); var days = days - (weeks * 2); // Handle special cases var startDay = startDate.getDay(); var endDay = endDate.getDay(); // Remove weekend not previously removed. if (startDay - endDay > 1) days = days - 2; // Remove start day if span starts on Sunday but ends before Saturday if (startDay == 0 && endDay != 6) days = days - 1 // Remove end day if span ends on Saturday but starts after Sunday if (endDay == 6 && startDay != 0) days = days - 1 return days; } workingDaysBetweenDates( new Date(StartDate) , new Date(EndDate))(Thanks to Martin Rolph from Oval Business Solutions who published this code in the Podio Beta Group)
Including the holidays can be done, but it's a bit more complicated (I've created a calculation with geman holidays).
Rainer
rg@delos-consulting.com -
@Rainer, I came across the same error as Fisher.
var startDate = @Project Start
var endDate = @Project Endfunction workingDaysBetweenDates(startDate, endDate) {
// Validate input
if (endDate < startDate)
return 0;// Calculate days between dates
var millisecondsPerDay = 86400 * 1000; // Day in milliseconds
startDate.setHours(0,0,0,1); // Start just after midnight
endDate.setHours(23,59,59,999); // End just before midnight
var diff = endDate - startDate; // Milliseconds between datetime objects
var days = Math.ceil(diff / millisecondsPerDay);// Subtract two weekend days for every week in between
var weeks = Math.floor(days / 7);
var days = days - (weeks * 2);// Handle special cases
var startDay = startDate.getDay();
var endDay = endDate.getDay();// Remove weekend not previously removed.
if (startDay - endDay > 1)
days = days - 2;// Remove start day if span starts on Sunday but ends before Saturday
if (startDay == 0 && endDay != 6)
days = days - 1// Remove end day if span ends on Saturday but starts after Sunday
if (endDay == 6 && startDay != 0)
days = days - 1return days;
}
workingDaysBetweenDates( new Date(StartDate) , new Date(EndDate)) -
Thank so much,
Dear @Rainer Grabowski,
thank you very much.
How can change time vacation: day unit to => hour time and minutes.
Example:
(Daily work 8 hours: 9:00-12:00, 13:00-18:00)
Vacation: Begin Friday 15:30; end: Tuesday 10:00
SumTime = 2h30m (Friday) + 8h00 (Monday) + 1h00 (Tuesday) = 11h30
I want the Result display in Calculation : 11h_30m
Thank you very much
-
Hi,
use the calculation above, but replace the last line and add this code:
var businessDays = workingDaysBetweenDates( new Date(startDate) , new Date(endDate))
// Handle special cases and calculate number of days with 8 hrs
var startDay = startDate.getDay();
var endDay = endDate.getDay();
var _8hrsDays =
(startDay != 0 && startDay != 6 && endDay != 6 && endDay != 0) ? 2 :
((startDay == 0 || startDay == 6) && (endDay == 6 || endDay == 0)) ? 0 : 1var _8hrsDays = (businessDays-_8hrsDays)*8
//Get Difference to UTC - enter your timezone in .tz(). If your timezone is UTC- change "add" to "substract" in the vars startHour and endHourvar tc = parseFloat(moment(startDate).tz("Europe/Berlin").format("ZZ"))/100;// Define Start/End times and calculate differences for these days
var workStartMorning = 9;
var workEndMorning = 12;
var workStartAfternoon = 13;
var workEndAfternoon = 18;var startHour = moment(startDate).add(tc,"h").hours()
var startMin = moment(startDate).minutes();
var startDiff = (moment([2000,0,1,workEndAfternoon,00])- moment([2000,0,1,startHour,startMin]))/3600000
var endHour = moment(endDate).add(tc,"h").hours()
var endMin = moment(endDate).minutes()
var endDiff = (moment([2000,0,1,endHour,endMin]) - moment([2000,0,1,workStartMorning,00]))/3600000
var vacStart = startHour <= workEndMorning ? startDiff - 1 : startDiff
var vacEnd = endHour < workStartAfternoon ? endDiff : endDiff -1var vacStart = startDay != 0 && startDay != 6 ? vacStart : 0;
var vacEnd = endDay != 0 && endDay != 6 ? vacEnd : 0;// Result
var resultNumber = vacStart + vacEnd + _8hrsDays; // Result is a number of hours e.g 11.5
var resultString = Math.floor(resultNumber) + " hrs _ " + (resultNumber*60)%60 + "min" // Result is a string e.g. 11h_30_m
If you want the result as a number (e.g. for further calculations) enter as last line:
resultNumber
else
resultString
But be aware: Once saved, the calculation field keeps it's format (string, number, date), you can't change it (you would have to create a new one). Maybe it's possible to do that all in the function, but this one works for me.
Rainer
rg@delos-consulting.com -
You've first described a use case, where the break is from 12-13, start 9 and end 18, workday is 8 hours. That does the code. When you change the lunch break to 12-13:30 you won't have an 8 hour workday (but 7.5 hours) and other parameters must be changed too. That's easy to do - here
var _8hrsDays = (businessDays-_8hrsDays)*8
8 to 7.5
here
var workStartAfternoon = 13;
13 to 13.30
and here
var vacStart = startHour <= workEndMorning ? startDiff - 1 : startDiff
var vacEnd = endHour < workStartAfternoon ? endDiff : endDiff -1-1 to -1.5
It's always helpful to have an exact description.
Rainer
rg@delos-consulting.com -
Dear @Rainer Grabowski,
i try again with your old code (break time from 12.00 to 13.00) but run into some problem.
- Start day : 08.03.2016 10.00
- End day: 08.03.2016 13.00
- The correct result should be 2 hours, but i got 3 hours instead.
My time zone is: tz("Asia/Phnom Penh")
Can you check the code and help me again?
Would be very thankful for that!
-
Dear @Rainer Grabowski,
Finally got it work correctly! Have to change some code
// Define Start/End times and calculate differences for these days
var workStartMorning = 9;
var workEndMorning = 12;
var workStartAfternoon = 13.5; // not 13.30
var workEndAfternoon = 17.5; // not 17.30and
var startDiff = (moment([2000,0,1,17,30])- moment([2000,0,1,startHour,startMin]))/3600000
//the old code is: var startDiff = (moment([2000,0,1,workEndAfternoon,00])- moment([2000,0,1,startHour,startMin]))/3600000and
startHour = startHour + startMin/60;
endHour = endHour + endMin/60;
var vacStart = startHour <= workEndMorning ? startDiff - 1.5 : startHour < workStartAfternoon ? startDiff - (workStartAfternoon-startHour): startDiff
//the old code is: var vacStart = startHour <= workEndMorning ? startDiff - 1.5 : startDiff
var vacEnd = endHour >= workStartAfternoon ? endDiff -1.5 : endHour > workEndMorning ? endDiff- (endHour - workEndMorning) : endDiff;
//the old code is: var vacEnd = endHour < workStartAfternoon ? endDiff : endDiff -1.5and final
var resultNumber = (vacStart + vacEnd + _8hrsDays).toFixed(2); // since i got error when resultNumber=0
Thank you for all your help! Truly Savior!
-
Dear @Rainer Grabowski,
I have 2 new problem here that i hope you could help out
FIRST:
The about code work fine when startDate and endDate in two different field.
But when start and end day is in the same Date field (start- end date field), how can i separate them?
SECOND:
i trying to get the weekday of a Date field, include date and time (field @Date). The code is like this:
var weekday = moment(@Date).isoWeekday();
weekday == 1 ? 2 :
weekday == 2 ? 3 :
weekday == 3 ? 4 :
weekday == 4 ? 5 :
weekday == 5 ? 6 :
weekday == 6 ? 7 :
weekday == 7 ? 8 :
0The code work fine when the time is after 7h00, but return 1 day ealier if the time is before 7h00
Correct:
Incorrect
I know it has to be some thing wrong with the timezone but don't know how to fix it, my timezone is Asia/Phnom_Penh
Can you help this?
Thank you for your time!
-
First of all:Your changes to the code can cause at least some problems, when Start time or end time is 13:30 or between 13:30 and 13:49 (or 17:30 and 17:49). 13.5. and 17.5 is wrong. You have to read it as numbers like : 17.50 and 13.50. I would change it to 13.30 and 17.30 and add 2 vars:
var startHourNmb = startHour + "." + startMin;
var endHourNmb = endHour + "." + endMin;right under var endDiff. And then modify the next 2 vars to :
var vacStart = startHourNmb < workEndMorning ? startDiff - 1.5 : startDiff
var vacEnd = endHourNmb < workStartAfternoon ? endDiff : endDiff -1.5Did you change var _8hrsDays to *7 - cause you've now a 7 hour day, not an 8 hours day.
To your questions:
First: You can't separate Start and end date, if both are in the same field. If you need them in the same field, you have to add an extra date field. With Globliflow you then can create a flow, which pulls the end date from the first date field and puts it into the extra field. Then you canuse this extra field for the calculation.Second: Do the same as in the other calculation. Use the var tc from the other calculation and then
var weekday = moment(@Date).add(tc, "hours").isoWeekday();
Rainer
-
A little addition. I've found that startdate at 28th and enddate at 3rd would take 6 days and 1 weekend, but won't substract the 2 weekend days. I've updated the line in the first script:
if (startDay - endDay > 1 || moment(startDate).format("D") - moment(endDate).format("D") > 1
@rainer does this seem correct to you?
complete script
var startDate = @XXX
var endDate = @XXX
if(startDate != null && endDate != null){function workingDaysBetweenDates(startDate, endDate) {
// Validate input
if (endDate < startDate)
return 0;// Calculate days between dates
var millisecondsPerDay = 86400 * 1000; // Day in milliseconds
startDate.setHours(0,0,0,1); // Start just after midnight
endDate.setHours(0,0,0,1); // End just before midnight
var diff = endDate - startDate; // Milliseconds between datetime objects
var days = Math.ceil(diff / millisecondsPerDay);// Subtract two weekend days for every week in between
var weeks = Math.floor(days / 7);
var days = days - (weeks * 2);// Handle special cases
var startDay = startDate.getDay();
var endDay = endDate.getDay();// Remove weekend not previously removed.
if (startDay - endDay > 1)
days = days - 2;// Remove start day if span starts on Sunday but ends before Saturday
if (startDay == 0 && endDay != 6)
days = days - 1// Remove end day if span ends on Saturday but starts after Sunday
if (endDay == 6 && startDay != 0)
days = days - 1return days;
}}
workingDaysBetweenDates( new Date(startDate) , new Date(endDate)) -
It's been a while since i looked at this, so not 100% sure. But this script seems to only look at the amount of days in a week, not the actual days. So the script behaves like this: if the entire given period is 5 days, do nothing, if it's 8, subtract 2 days, etc... Roughly...
So i think it's just a matter of changing 2 day weekend into 1 day. Extensive testing recommended! Especially not sure about last 4 changed lines, it's confusing:)
---Workaholic / slave-driver(jk :) edition--- (//CHANGE) shows changed linesvar startDate = @XXX
var endDate = @XXX
if(startDate != null && endDate != null){function workingDaysBetweenDates(startDate, endDate) {
// Validate input
if (endDate < startDate)
return 0;// Calculate days between dates
var millisecondsPerDay = 86400 * 1000; // Day in milliseconds
startDate.setHours(0,0,0,1); // Start just after midnight
endDate.setHours(0,0,0,1); // End just before midnight
var diff = endDate - startDate; // Milliseconds between datetime objects
var days = Math.ceil(diff / millisecondsPerDay);// Subtract two weekend days for every week in between
var weeks = Math.floor(days / 7);
var days = days - (weeks * 1); //CHANGE// Handle special cases
var startDay = startDate.getDay();
var endDay = endDate.getDay();// Remove weekend not previously removed.
if (startDay - endDay > 1)
days = days - 1; //CHANGE// Remove start day if span starts on Thursday but ends before Friday //CHANGE
if (startDay == 4 && endDay != 5) //CHANGE
days = days - 1// Remove end day if span ends on Friday but starts after Thursday //CHANGE
if (endDay == 5 && startDay != 4) //CHANGE
days = days - 1return days;
}}
workingDaysBetweenDates( new Date(startDate) , new Date(endDate)) -
Hi Roman,
my code you mentioned I published a long time ago. I don't use it anymore cause I've developed a simpler one:// COUNT NUMBER OF DAYS IN GIVEN TIME PERIOD W/O FRIDAYS
var start = @start date field;
var end = @end date field;
var dur = moment(end).diff(start,"d")+1
var count = 0;
for(var i = 0; i < dur; i++){
day = moment(start).add(i,"d").isoWeekday();
if(day != 5){
count += 1;
}};
countIf f.ex. start date = 05/10/2018 (Thursday) and end date = 05/19/2018 (Saturday) this should return 8.
But take care: That code only counts the number of days w/o Fridays - public/bank holidays aren't calculated (that needs a more elaborated code which I've developed for multiple countries/regions).Rainer
rg@delos-consulting.com -
Just small update if someone needs:
var start = @Date of request;
var end = @Date of visit;
var dur = moment(end).diff(start,"d")
var count = 0;
for(var i = 0; i < dur; i++){
day = moment(start).add(i,"d").isoWeekday();
if(day != 5){
count += 1;
}};//doesn't show "0" in Duration filed
start == null || end == null ? "" : count -
Please Who Can help me?
Calculate working days between two dates (excepts holidays)
I have this one and somethings is wrong :-(
$(document).ready(() => {
$('#calc').click(() => {
var d1 = $('#d1').val();
var d2 = $('#d2').val();
$('#dif').text(workingDaysBetweenDates(d1,d2));
});
});//* Two working days and an sunday (not working day) */
var holidays = ['24-12-2019', '25-12-2019', '26-12-2019'];
var startDate = @Days/time out office;
var endDate = @Return to office;// Validate input
if (endDate < startDate) {
return 0;
}// Calculate days between dates
var millisecondsPerDay = 86400 * 1000; // Day in milliseconds
startDate.setHours(0, 0, 0, 1); // Start just after midnight
endDate.setHours(23, 59, 59, 999); // End just before midnight
var diff = endDate - startDate; // Milliseconds between datetime objects
var days = Math.ceil(diff / millisecondsPerDay);// Subtract two weekend days for every week in between
var weeks = Math.floor(days / 7);
days -= weeks * 2;// Handle special cases
var startDay = startDate.getDay();
var endDay = endDate.getDay();
// Remove weekend not previously removed.
if (startDay - endDay > 1) {
days -= 2;
}
// Remove start day if span starts on Sunday but ends before Saturday
if (startDay == 0 && endDay != 6) {
days--;
}
// Remove end day if span ends on Saturday but starts after Sunday
if (endDay == 6 && startDay != 0) {
days--;
}
/* Here is the code */holidays.forEach(day => {
if ((day >= d0) && (day <= d1)) {
/* If it is not saturday (6) or sunday (0), substract it */
if ((parseDate(day).getDay() % 6) != 0) {
days--;
}
}
});
return days;
}
startDate == null || endDate == null ? "" : workingDaysBetweenDates( new Date(startDate) , new Date(endDate))
Please sign in to leave a comment.
Comments
27 comments