Calculate Business Days Query

Shweta

Active Member
Hi,

I have a requirement, where I need to perform the below calculation on dates. Can anyone please advise.

1. From a website portal, I am fetching an index date and now i need to check the condition, if that Index Date > 3 Business days.
second condition is: Index Date > 11 Business days
Now, this business day should not include holidays and weekends. So, can anyone please tell me how can i make this calc?

Also, I have to send email on 11th Business day, if the above condition does not met. So, is it possible to do so, that bot is running today and it should send email after 11 Business days. IF yes, then pls advise how?

Thanks!
 

conicle

New Member
There is a function in BP you first need to create the Calendar in the system manager. It lets you use all holidays in the UK or a few other countries but there is an option to add all your own dates. so once you have your index date (lets say Thursday) 23/0/2109 then use the function to add 3 working days. the result is 29\05\2019 as there is a bank holiday on the coming Monday (As selected in the dates)

You can then check if it is within by comparing the dates.

Let us know if that solves it.
View attachment Calenders 2.PNG

View attachment Calenders.PNG

View attachment Screenshot (3).png
 

conicle

New Member
The best solution would be to request from the person who does have access to create for you. Otherwise you can just check to see if the calendar is already in place by using one of the function and see if you get an error.

If you really needed to do It long hand and you dont want to use Excel functions to add days you can check what day of the week it is buy using 'FormatDate([Date], "dddd")' which will tell you what day it is then just use a look to add days and if it is a weekend you can add extra. You can also add a check to see if it matches any of the public holidays in the UK with a decision stage and then add extra that way. Its extremely long winded but it would work. I suggest requesting the Calender to be added.

It is possible to add the Permission to the developer tree in User roles but then all accounts with the developer role would be able to change
 

windfondquist

New Member
Hi,

I have a requirement, where I need to perform the below calculation on dates. Can anyone please advise.

1. From a website portal, I am fetching an index date and now i need to check the condition, if that Index Date > 3 Business days.
second condition is: Index Date > 11 Business days
Now, this business day should not include holidays and weekends. So, can anyone please tell me how can i make this calc?

Also, I have to send email on 11th Business day, if the above condition does not met. So, is it possible to do so, that bot is running today and it should send email after 11 Business days. IF yes, then pls advise how on working day calculator?

Thanks!
I need help with a formula I am trying to use in my ETL to calculate how many business days there have been YTD. I was using this and it seemed to work, however, it is now returning a result of 1. Can anyone assist?

(DATEDIFF(DATE(CURRENT_DATE()),DATE(CONCAT(DATE_FORMAT(CURRENT_DATE(),'%Y-%m-'),'01'))) -
((WEEKOFYEAR(CURRENT_DATE())-WEEKOFYEAR(CONCAT(DATE_FORMAT(CURRENT_DATE(),'%Y-%m-'),'01')))*2)-
(CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 7 THEN 1 ELSE 0 END) -
(CASE WHEN DAYOFWEEK(CONCAT(DATE_FORMAT(CURRENT_DATE(),'%Y-%m-'),'01')) = 1 THEN 1 ELSE 0 END))
 
Top