Hi everyone,

I have one doubt.

I need to implement an expression/function between dates to calculate the vacations of the employees, but i need to take out the weekend days( saturday and sunday).

I use a DiffDays() and this calculated the difference between the dates but included the weekend days.

How I can take out the weekend days?



Something like this should do the trick:

Create an action like this with the output as an Integer (turn it into a function if you want to use it inside expressions):


Basically it starts at DateTime1 and counts manually the days up to DateTime2 if those days aren't weekends. I did some tests and it seems to work fine, but if you understand the logic you should also be able to edit it to fit more specific needs. Let me know if that works!

Just a small improvement into Francisco idea:

This is for holidays, so the number of days is small, a week or two each time. But imagine this is for longer periods: a leave or a sabbatical. No need to do all the math day by day.

Imagine DiffDays returns 180.

Trunc 180/7 to know the number of weeks. (25, so 125 week days)

Add 25*7 days to your date (175) and just check the final 5 days to see if they are weekend or not.


So, do this before Francisco's flow and you just saved 175 loops in 180 days.


If you also need to consider holidays, the answer is a bit longer. You need to create a list with all the holidays and check if your interval includes any holiday that is not on weekend.

Nuno's idea is great to avoid all the unnecessary iterations (assuming DiffDays runs on constant time). The difference is negliglble for a few calls, but if you are going to use it constantly (e.g. logic that is running 24/7 with potentially large timespans) then you should do your best to optimize it. I also noticed that my flow above does not work for negative differences (which you might need for your use case), so that can be corrected easily too.

To generalize these ideas to any situation, you can do the following (DiffIsNegative is of type Boolean, FullWeeks is of type Integer and TempDateTime is of type DateTime):

Now, assuming you implemented the flow in my previous post as DiffDaysWithoutWeekends, you can now create the following "improved" flow:


So it is exactly the same core idea but with the additional considerations for handling the negative case, and optimizing the number of iterations by applying Nuno's method to the general case.

These assignments ("Initialize") are used to apply Nuno's suggestion, along with the final "Add Full Week Weekdays" assignment before the end. Just consider, for every full week that has passed (7 full days), 5 of those days must be non-weekend days; therefore in the end we need to add (# of Full Weeks * 5) to the final count:

These assignments ("Swap DT1-DT2 and Mark as Negative") are needed to handle the negative cases, along with the "DiffShouldBeNegative" subflow towards the end, basically making it count backward instead of forward:

We do not need to multiply the (FullWeeks * 5) by (-1) in the negative case because FullWeeks can already be a negative number since it is calculated using the built-in DiffDays(), so we don't need to take care of that part.

Hope all of this makes sense! In the end the optimizations may not be needed at all, and there might be better ways to present/simplify the flow (such as creating a single action with all the necessary logic), but this should cover all the main points. I got a bit overly invested into the problem so I figured I might as well share my efforts lol :)


Francisco Calderón wrote:

Something like this should do the trick:

Create an action like this with the output as an Integer (turn it into a function if you want to use it inside expressions):


Basically it starts at DateTime1 and counts manually the days up to DateTime2 if those days aren't weekends. I did some tests and it seems to work fine, but if you understand the logic you should also be able to edit it to fit more specific needs. Let me know if that works!

 Hi Francisco,


Thanks for your help.


My Data Type is only Date.

When I create this action, put in an expression and publish I can´t see my application on website. 


I dont´know if I´m doing something wrong (I´m a trainee in OutSystems platform).

 

Nuno Reis wrote:

Just a small improvement into Francisco idea:

This is for holidays, so the number of days is small, a week or two each time. But imagine this is for longer periods: a leave or a sabbatical. No need to do all the math day by day.

Imagine DiffDays returns 180.

Trunc 180/7 to know the number of weeks. (25, so 125 week days)

Add 25*7 days to your date (175) and just check the final 5 days to see if they are weekend or not.


So, do this before Francisco's flow and you just saved 175 loops in 180 days.


If you also need to consider holidays, the answer is a bit longer. You need to create a list with all the holidays and check if your interval includes any holiday that is not on weekend.

 Hi Nuno,


Thanks for your help.

"Weeks" is a local variable?


My Data Type is only Date.

When I create this action, put in an expression and publish I can´t see my application on website. 


I dont´know if I´m doing something wrong (I´m a trainee in OutSystems platform).

 

Francisco Calderón wrote:

Nuno's idea is great to avoid all the unnecessary iterations (assuming DiffDays runs on constant time). The difference is negliglble for a few calls, but if you are going to use it constantly (e.g. logic that is running 24/7 with potentially large timespans) then you should do your best to optimize it. I also noticed that my flow above does not work for negative differences (which you might need for your use case), so that can be corrected easily too.

To generalize these ideas to any situation, you can do the following (DiffIsNegative is of type Boolean, FullWeeks is of type Integer and TempDateTime is of type DateTime):

Now, assuming you implemented the flow in my previous post as DiffDaysWithoutWeekends, you can now create the following "improved" flow:


So it is exactly the same core idea but with the additional considerations for handling the negative case, and optimizing the number of iterations by applying Nuno's method to the general case.

These assignments ("Initialize") are used to apply Nuno's suggestion, along with the final "Add Full Week Weekdays" assignment before the end. Just consider, for every full week that has passed (7 full days), 5 of those days must be non-weekend days; therefore in the end we need to add (# of Full Weeks * 5) to the final count:

These assignments ("Swap DT1-DT2 and Mark as Negative") are needed to handle the negative cases, along with the "DiffShouldBeNegative" subflow towards the end, basically making it count backward instead of forward:

We do not need to multiply the (FullWeeks * 5) by (-1) in the negative case because FullWeeks can already be a negative number since it is calculated using the built-in DiffDays(), so we don't need to take care of that part.

Hope all of this makes sense! In the end the optimizations may not be needed at all, and there might be better ways to present/simplify the flow (such as creating a single action with all the necessary logic), but this should cover all the main points. I got a bit overly invested into the problem so I figured I might as well share my efforts lol :)


 

 Hi Francisco,


Thanks for your help.


My Data Type is only Date.

When I create this action, put in an expression and publish.

When I see the aplication on a website my expression DiffDaysWithoutWeekends = 0 in all records


I dont´know if I´m doing something wrong (I´m a trainee in OutSystems platform).

 

Solution

BigBunny9 wrote:

 Hi Francisco,


Thanks for your help.


My Data Type is only Date.

When I create this action, put in an expression and publish.

When I see the aplication on a website my expression DiffDaysWithoutWeekends = 0 in all records


I dont´know if I´m doing something wrong (I´m a trainee in OutSystems platform).

Hi BigBunny, you're welcome!

If your Data Type is Date you can use the built in DateToDateTime() to convert it before passing it as an argument as a solution, such as DiffDaysWithoutWeekends(DateToDateTime(MyDate1), DateToDateTime(MyDate2)) inside an expression. Alternatively, you can change the input data types and make the conversion within the flows, I think it's necessary because the DiffDays function takes 2 DateTime arguments so you probably can't work with Date types directly.

Make sure you understand the steps and that all the flows are copied correctly. I tested it in a mobile application and it seems to work fine for me, sending you the OML as an attachment in case it helps. If all fails then the problem could be related to the variables you are using or how you're referencing them from your screen.

Hope that works for you!

 

Solution

Francisco Calderón wrote:

BigBunny9 wrote:

 Hi Francisco,


Thanks for your help.


My Data Type is only Date.

When I create this action, put in an expression and publish.

When I see the aplication on a website my expression DiffDaysWithoutWeekends = 0 in all records


I dont´know if I´m doing something wrong (I´m a trainee in OutSystems platform).

Hi BigBunny, you're welcome!

If your Data Type is Date you can use the built in DateToDateTime() to convert it before passing it as an argument as a solution, such as DiffDaysWithoutWeekends(DateToDateTime(MyDate1), DateToDateTime(MyDate2)) inside an expression. Alternatively, you can change the input data types and make the conversion within the flows, I think it's necessary because the DiffDays function takes 2 DateTime arguments so you probably can't work with Date types directly.

Make sure you understand the steps and that all the flows are copied correctly. I tested it in a mobile application and it seems to work fine for me, sending you the OML as an attachment in case it helps. If all fails then the problem could be related to the variables you are using or how you're referencing them from your screen.

Hope that works for you!

 

 Hi Francisco,


Thanks for your help and work!!!