Calculating workdays

Comments

27 comments

  • Rainer Grabowski (FVM)

    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

    1
    Comment actions Permalink
  • Flemming Agner Jørgensen

    @rainer
    Thx for your very kind help.
    I will check it out asap.
    Have a nice weekend :-)

    0
    Comment actions Permalink
  • Fisher Gulle

    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 ?

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    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.

    1
    Comment actions Permalink
  • Andrew Hutchins

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

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    Hi Andrew, hi Fisher,

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

    Rainer

    0
    Comment actions Permalink
  • admin

    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

    0
    Comment actions Permalink
  • Rainer Grabowski

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

    2
    Comment actions Permalink
  • admin

    Super, 

    i will add code and report

    Thank you for your cool support.

    Have nice day

     

    0
    Comment actions Permalink
  • admin

    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?

    0
    Comment actions Permalink
  • Rainer Grabowski

    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

    0
    Comment actions Permalink
  • admin

    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!

    0
    Comment actions Permalink
  • Rainer Grabowski

    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

    0
    Comment actions Permalink
  • admin

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

    0
    Comment actions Permalink
  • admin

    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?

    Thank you for your time!

     

     

    0
    Comment actions Permalink
  • Rainer Grabowski

    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.5

    Did 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

    rg@delos-consulting.com

    0
    Comment actions Permalink
  • Felix Lepoutre

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

    0
    Comment actions Permalink
  • Roman Sydorchuk

    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?

    0
    Comment actions Permalink
  • Felix Lepoutre

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

    0
    Comment actions Permalink
  • Roman Sydorchuk

    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.

    0
    Comment actions Permalink
  • Felix Lepoutre

    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. 

    1
    Comment actions Permalink
  • Roman Sydorchuk

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

    0
    Comment actions Permalink
  • Rainer Grabowski

    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;
    }};
    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

    1
    Comment actions Permalink
  • Roman Sydorchuk

    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

    0
    Comment actions Permalink
  • Roman Sydorchuk

    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

    0
    Comment actions Permalink
  • Rainer Grabowski

    "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

    0
    Comment actions Permalink
  • Kathrine N. Hansen

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

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk