Getting Non-working days using Calculation Widget

  • Profile Image
    Asked on April 26, 2015 at 04:09 AM

    Hi, I am working on the working days problem (Date Difference plugin does not work).


    I am using the Calculation Wizard in order to calculate the simple difference between two dates and then I would like to use conditions in order to subtract the number of not working days.


    Using the Conditions Wizard I can check if the “Ticket Date” is in a Saturday (2015/04/11) or between the 2015/04/11 and today and in this case I would like to increment a counter (not working days).


    In the Conditions Wizard I am using “ Update a form field value”. Conditions work well but I cannot increment/decrement the not working days filed (works like a text but does not increment/decrement).


    Any Hint?




  • Profile Image
    Answered on April 26, 2015 at 12:33 PM

    Are you trying to get non-working days in some period of time if user select start date - end date?

    Problem is that I do not understand exactly what you are trying to achieve with counter? What logic you are using to count non-working days between two dates?

    What I have on my mind how it is possible to get non working days between two dates is to set condition like this:

    Considering that there are 7 days in one week and if there are 5 working days and 2 non working days then there is 28.57% of non working days. And I added that inside of calculation. But please note this calculation is not 100% accurate. 

    Here is my demo form that is calculating non-working days: 

    Hope this will help. Let us know if you need further assistance. 

  • Profile Image
    Answered on July 20, 2015 at 06:03 AM

    That doesn't work. For example, Monday's date to Friday's date in the same week is generally 5 working days (no intervening weekend or public holiday) but this formula would always give 1 day.

    Also, correct calculation of working days is dependent on the locale (public holidays, Muslim countries where the weekend is Friday and Saturday, etc) and the time of day in that locale (generally 9 to 5). 

    I would also like this function but it is not a trivial problem and requires the development of a widget. Is this something that a developer would consider? Even a simple 'Monday to Friday' would still be very useful.

  • Profile Image
    Answered on July 20, 2015 at 09:51 AM

    I would just like to repeat what I think that you are after so that we can go over this and help you set it all up.

    1. Capture date frame / timeframe for which we would count the non working days for
    2. get the number of days in the given timeframe
    3. Get the number of non working days within these days
    4. Calculate all of the holidays that are within this same time frame as well

    Now looking at the list above, the first would require 2 DateTime fields so that we can capture the time frame

    The second point would be easy by subtracting the selected number of days from the current day and driving it through the absolute function to get the positive number all the time - no matter if the date selected is in the past or in the future.

    The 3rd one is however a bit difficult because there are 2 days in a week that we do not want to include, but to see if the date is added as a Monday (where there are exactly 5 working days and 2 non working) or if the date entered is Friday (where there is 1 working day and 2 non working days of that week), is something that would be rather complex (possible) to do with Form Calculation.

    For example you must:

    1. Check what day of the week it is on the form field that can be changed (on both if both can).
    2. if both can there is a lot more complexity added to the form to calculate this
    3. Determine if you will check past data, future dates or both - since each must be done differently, and if both, that adds a lot of complexity on its own

    Now since you also want to calculate all of the holidays in as well, this would add a lot of complexity to the form calculation as well since you would need to:

    1. Add date fields with the default values set as the holidays you want to match against
    2. add a field that will be updated by the conditions to show how many dates are within that time frame
    3. use that field in the Form Calculation

    Now as mentioned above, this is quite possible to do, but would require quite possibly several hours to make and test out due to its complexity - and means that it is very likely to take a loot more time for someone with a different idea or that is not sure how to begin.

    I am saying this only because the time that will take to make this is about the same time that would take to fix the widget while the widget offers much more, it is better optimized for speed and can be reused.

    To copy this to another form would take just about the same time as it would take to make it the first time.

    So what I would suggest is to wait until our developers update this thread: with the resolution of the bug. Since it is given important status and developer is already assigned to it, I expect that you will hear back from him soon, but I can not tell you any specific time frame.

  • Profile Image
    Answered on July 20, 2015 at 01:04 PM
    Thank you for your reply.
    I will have a go using the modulo of the serial date number to determine the first and last days, then get the whole number of weeks having 2-day weekends. Then multiply that number by 5 and add back the leading and trailing days to the result. Something like that.
    I don’t think the public holidays is feasible given the dependence on locale, not to mention countries where the weekend is Friday and Saturday. But a basic working day calculation would be something that most could work with.
  • Profile Image
    Answered on July 20, 2015 at 02:29 PM

    Just to add, if you are familiar with Javascript or another programming language, this could be possible if you'll use the form's full source code.

    I see a similar discussion in this link, you can then use the Javascript function getDay() to identify the day of that date. This might be too technical and will need more time to research, but I hope it gives you an idea.

  • Profile Image
    Answered on July 20, 2015 at 06:53 PM

    Thanks Charlie, I wasn't aware that you can get the forms full source code. That would make it quite straightforward to do. (I see it can't be done with the form calculation).