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
Thx for your very kind help.
I will check it out asap.
Have a nice weekend :-)

Hi @rainer, I tried using your code for my app and I am getting this error - "Script reference error: StartDate is not defined". I have a field named StartDate and EndDate. Can you please help ?

Hi Fisher,

maybe there is a typo in your code or one line is missing. Copy the content of your calculation field and paste it here, than I'll have a look at it.

@Rainer, I came across the same error as Fisher.

var startDate = @Project Start
var endDate = @Project End

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))

Hi Andrew, hi Fisher,

try in the last line "start" and "end" with lower first character:
workingDaysBetweenDates( new Date(startDate) , new Date(endDate))

Rainer

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 hrsvar 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 : 1`
`var _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 endHour`
`var 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]))/3600000var 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 : startDiffvar vacEnd = endHour < workStartAfternoon ? endDiff : endDiff -1`
`var 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

Super,

i will add code and report

Thank you for your cool support.

Have nice day

Dear @Rainer Grabowski

The code you gave worked great! Thank you very much.

But now  there is a problem that: i have a lunch break from 12pm to 13h30, that,s not counted in to working time.

For example: 14h00 - 11h00  = 1hs and 30 mins (working time)

Can you help me with this?

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 : startDiffvar 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!

HI,

you've to to concatenate Phnom and Penh by an underscore, that's the needed format for city names with 2 words.

.tz("Asia/Phnom_Penh")

Rainer

Dear @Rainer Grabowski,

Finally got it work correctly! Have to change some code

`// Define Start/End times and calculate differences for these daysvar workStartMorning = 9;var workEndMorning = 12;var workStartAfternoon = 13.5; //  not 13.30var workEndAfternoon = 17.5; // not 17.30`

and

`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]))/3600000`

and

`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 : startDiffvar vacEnd = endHour >= workStartAfternoon ? endDiff -1.5 : endHour > workEndMorning ? endDiff- (endHour - workEndMorning) : endDiff;//the old code is: var vacEnd = endHour < workStartAfternoon ? endDiff : endDiff -1.5`

and 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 :0`

The 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?

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 : startDiffvar vacEnd = endHourNmb < workStartAfternoon ? endDiff : endDiff -1.5`

Did you change var _8hrsDays to *7 - cause you've now a 7 hour day, not an 8 hours day.

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

rg@delos-consulting.com

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 - 1

return days;
}}
workingDaysBetweenDates( new Date(startDate) , new Date(endDate))

Hi guys

I would like to do small modification in code - We have 6 working days from Saturday till Thursday and one day off is Friday. Can you just explain me where in the above code I have to change something please?

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 lines

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 * 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 - 1

return days;
}}
workingDaysBetweenDates( new Date(startDate) , new Date(endDate))

thank you Felix but it doesn't work with me I tried before... so I confused :(

just for example if you choose Start date Saturday and End date Sunday it calculates duration in 0 days which is incorrect.

Very sorry Roman, this would take a while to figure out since it's becoming quite complex and paid work is piling up right now.

I've found that code of Mr Rainer doesn't work correctly as well, it changes Weekend on one day back every week, perhaps I do something wrong...

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 FRIDAYSvar start = @start date field;var end = @end date field;var dur = moment(end).diff(start,"d")+1var count = 0;for(var i = 0; i < dur; i++){day = moment(start).add(i,"d").isoWeekday();if(day != 5){count += 1;}};count`

If 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

Dear Rainer,

Thank you a lot, it works great with me!

Is your elaborated code (for multiple countries/regions) publicly available in the site (here)?

Roman

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++){
if(day != 5){
count += 1;
}};

//doesn't show "0" in Duration filed
start == null || end == null ? "" : count

"Is your elaborated code (for multiple countries/regions) publicly available in the site (here)? "

No, sorry. I developed it for some clients who paid for it. I only sell that code.

Rainer

I have this one and somethings is wrong :-(

\$('#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;

// 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))