4 réponses
Hello,
I know it's late, but I also tried to calculate this in BO and here is the solution I came up with. However, it does not account for public holidays:
To calculate the number of working days between Date No. 1 and Date No. 2:
Create the following 3 variables:
[Start Day] = DayOfWeek([Date No. 1])
[End Day] = DayOfWeek([Date No. 2])
[Days Between] = DaysBetween([Date No. 1];[Date No. 2])
Number of working days between Date No. 1 and Date No. 2
=If(Mod([Days Between]+[Start Day]-[End Day];7)=0;[Days Between]-(([Days Between]+[Start Day]-[End Day])/7)*2; [Days Between]-(Truncate(([Days Between]+[Start Day]-[End Day])/7;0)+1)*2)
Good luck to everyone.
I know it's late, but I also tried to calculate this in BO and here is the solution I came up with. However, it does not account for public holidays:
To calculate the number of working days between Date No. 1 and Date No. 2:
Create the following 3 variables:
[Start Day] = DayOfWeek([Date No. 1])
[End Day] = DayOfWeek([Date No. 2])
[Days Between] = DaysBetween([Date No. 1];[Date No. 2])
Number of working days between Date No. 1 and Date No. 2
=If(Mod([Days Between]+[Start Day]-[End Day];7)=0;[Days Between]-(([Days Between]+[Start Day]-[End Day])/7)*2; [Days Between]-(Truncate(([Days Between]+[Start Day]-[End Day])/7;0)+1)*2)
Good luck to everyone.
Gavani
To be modified according to the weekends, taking an example of a Saturday as the starting day and a Tuesday as the end date, the number of days is not correct. However, the solution is close and it’s a very good lead, thank you!
Hello,
I encountered the same issue and found a workaround (not optimal at the moment) to at least remove the weekends.
I have a first column where I edited a variable using the "days between" function that gives me my total number of days. Variable Days: =DaysBetween(<max date="">, CurrentDate())
In the second column, I created a variable that gives me the corresponding number of weeks for my number of days. I used the "truncate" function with 0 decimal places to get just the number of whole weeks. Variable weeks = Truncate((<days>/7), 0)
Then a column with an approximate number of working days since holidays are not deducted. =<days> - (<weeks>*2)</weeks></days></days></max>
I encountered the same issue and found a workaround (not optimal at the moment) to at least remove the weekends.
I have a first column where I edited a variable using the "days between" function that gives me my total number of days. Variable Days: =DaysBetween(<max date="">, CurrentDate())
In the second column, I created a variable that gives me the corresponding number of weeks for my number of days. I used the "truncate" function with 0 decimal places to get just the number of whole weeks. Variable weeks = Truncate((<days>/7), 0)
Then a column with an approximate number of working days since holidays are not deducted. =<days> - (<weeks>*2)</weeks></days></days></max>
Hello,
Thank you for your response.
It’s a bit like what I did (using the DaysBetween function). However, what you provided is just an estimate, but unfortunately, we are required to be much more precise!! So I multiplied the variables to truly calculate based on the day number of the week, the number of days with all possible cases if there is more than one week between start day and end day or less than one week. There are still the public holidays: for the fixed ones, I think I’ll be able to manage, but for the variable ones, how do you handle that?
Thank you for your response.
It’s a bit like what I did (using the DaysBetween function). However, what you provided is just an estimate, but unfortunately, we are required to be much more precise!! So I multiplied the variables to truly calculate based on the day number of the week, the number of days with all possible cases if there is more than one week between start day and end day or less than one week. There are still the public holidays: for the fixed ones, I think I’ll be able to manage, but for the variable ones, how do you handle that?
Hello Max,
Unfortunately, my solution is very complex and I will have a hard time explaining it to you in detail. Besides, this BO report was done quite a while ago and I would need to dive back into it !!
Basically, I used the functions:
- Week (week number)
- DaysBetween (number of days between 2 dates)
- DayOfTheWeekNumber
With plenty of tests on the start and end dates to determine if there is more than 1 week between the two etc...
I didn't go as far as accounting for holidays...
But I think all of this requires a complicated solution while in Excel the formula already exists!!
I find it crazy that there is no equivalent in BO...
Good luck!
Janice
Unfortunately, my solution is very complex and I will have a hard time explaining it to you in detail. Besides, this BO report was done quite a while ago and I would need to dive back into it !!
Basically, I used the functions:
- Week (week number)
- DaysBetween (number of days between 2 dates)
- DayOfTheWeekNumber
With plenty of tests on the start and end dates to determine if there is more than 1 week between the two etc...
I didn't go as far as accounting for holidays...
But I think all of this requires a complicated solution while in Excel the formula already exists!!
I find it crazy that there is no equivalent in BO...
Good luck!
Janice
Hello
This is an old post, but having struggled with the subject, here’s what I did. My problem was calculating a due date based on today’s date while only considering working days.
I listed all the public holidays without taking into account those that fall on the weekend. I took their date (the day number of the year), and for my calculation, I set a condition to check if any of the days belonged to this list... etc... My BO state works, but I will need to update the list every year.
Another solution would be to use an SQL function and retrieve its result in BO. There you go.
This is an old post, but having struggled with the subject, here’s what I did. My problem was calculating a due date based on today’s date while only considering working days.
I listed all the public holidays without taking into account those that fall on the weekend. I took their date (the day number of the year), and for my calculation, I set a condition to check if any of the days belonged to this list... etc... My BO state works, but I will need to update the list every year.
Another solution would be to use an SQL function and retrieve its result in BO. There you go.