Working days between 2 dates - errors with proposed formula
Hello All,
I used the suggested formula to calculate the # of weekdays between 2 dates
https://help.anaplan.com/21bf8e70-3193-4912-98c6-f25327c11d19-Excel-Equivalent-Formulas
and I noticed many errors when the end date was a monday (see attachment). Has anyone else also noticed that?
Also, I would like to propose a different formula (with the same line items)
Start Date
End Date
# of working days =
ROUND((End Date - Start Date + 1) / 7, 0, DOWN) * 5 + MOD(End Date - Start Date + 1, 7) - IF MOD(End Date - Start Date + 1, 7) = 0 THEN 0 ELSE IF WEEKDAY(Start Date) = 7 OR WEEKDAY(Start Date) < 7 AND WEEKDAY(Start Date) + MOD(End Date - Start Date + 1, 7) = 7 THEN 1 ELSE IF WEEKDAY(Start Date) < 7 AND WEEKDAY(Start Date) + MOD(End Date - Start Date + 1, 7) > 7 THEN 2 ELSE 0
If needed, "- Holidays" can be added at the end
Answers
-
Why not have a SYS Time module by day, create a line item for working day (boolean) and another line item if Workiing Day the 1 else 0. Then, in your target module (not using Time in the Applies To) create a line item using TimeSum( SYS Time.Working Days Number, Start Date, End Date).
Rob
0 -
Thank you @rob_marshall
I thought about it but wanted to avoid creating a SYS time by day.
I am comparing the # of working days for the same dates for the past 3 years and the next 2.
I could create a "large" time range that covers all 5 years. But then, all time formatted line items that end users use as a dropdown selection will include all those 5 years. I want to avoid that0 -
I can understand that, but that Boolean and the line item with a single IF THEN will most likely perform better than the multi level IF THEN statement that you have. Plus, it is way easier to maintain and explain what the logic is doing. As for the drop down, why not have them simply enter a date? Have two line items formatted as date, Start Date and End Date.
Again, I don’t exactly know what your requirements are, but I would definitely not have a drop down of 5 years at the day level.
0 -
Thank you @rob_marshall. I agree with all your comments. I am fairly up to date with best practices, the planual, PLANS and DISCO.
In this particular use case, using a system module is not a valid option due to the impact on the end user experience.
The primary intention of my message was to call out an issue with an "official" formula that Anaplan proposes to use (at least that's how I understand the formulas/info shared under help.anaplan.com). That formula contains 8 IF statetements and still return some errors. I am proposing to fix the formula with a simplified version.
For those that using the SYS module dimensioned by day is a viable option, I would highly recommend that solution, as you suggested it.
0 -
Just a quick correction to your syntax. When using dates (and not time periods) the syntax of the TIMESUM formula should be
TIMESUM( SYS Time.Working Days Number, End Date, Start Date).
You need to invert the dates!
This information is also available in this thread:
https://community.anaplan.com/t5/Idea-Exchange/Dates-in-Timesum/idi-p/89565
0
