How to Calculate Working Days Excluding Sunday from current month to next 18 months

0
can someone please explain how to calculate the working days excluding Sundays? i have to calculate working days from current month to next 18 months means till July 2025 i need working days for every month. if have any expression its good otherwise can some one explain microflow with image
asked
2 answers
1

This problem seems trivial at first, but turns out to be very complicated. Most people trying to solve this start out with thinking that you can just divide by 7 and multiply by 5 and accounting for the remainder. But then you realize you need to account for national holidays, in al countries that your app is used, for all future dates, and potentially for dates in the past.

 

My advice, choose one of these options:

  1. Use the Working Days module. Once you have the after startup microflow configured, you will have a list of days where all weekend days are non-working days and you can mark off national holidays by hand. The module has a microflow action that you can use to calculate the workingdays between two dates.
  2. Use the workingdays.org api or the timeanddate.com api to do the heavy lifting for you by creating an account and using the REST call. This saves you some work but might cost you some money.
  3. Implement your own solution. This will likely require some Java programming here and there and you would need to get a calendar from a source you trust.

Which option fits you best will depend on how often you need to do the calculation, how much time you want to spend maintaining a list of public holidays and how comfortable you are with coding things in a language like Java.

answered
0

The issue may have been resolved for you, but to any new developers with this issue:

 

The way I solved this is by creating a submicroflow and made use of the formatDateTime() function. 

 

Monday through Sunday can be expressed as integers by Mendix (Mon = 1 --> Sun = 7)

With this knowledge we can create any custom logic that runs through a series of dates, but takes out the sundays (or any other day you wish to remove)

 

Create a while loop (if you want a period) where the 'End date' (including all sundays) is the final condition and the 'Start date' is the iterator. Add a new variable $Counter starting at 0

In the loop always raise the iterator by one day by using the addDays($Iterator, 1) function. 

If the iterator is a sunday:  formatDateTime($Iterator, 'u') = '7'; we can up the $Counter by 1 everytime the condition is met until the loop is broken.

 

You'll end up with an integer that you can then calculate with. Did you want to only count the working days (Mon - Sat) in the given period? --> check the tot amount of days between the Start date and the Deadline/EndDate with daysBetween($StartDate, $EndDate) --> subtract the $Counter int from this result and you'll have successfully calculated the amount of working days for this time period

 

This approach benefits you because: 

- You don't need to download any modules, which in turn doesn't give you any extra dependencies in your project

 

Limitation:

Like Lennaert Goris above stated, this does not include any holidays , you'll have to build a more complicated extra check into this microflow AND you'll need to have base logic set up to recognize which days are holidays to begin with.

 

Hope this helps anyone who is curious about this and wants to have a go without having to download any new dependencies 

answered