Working days between 2 dates - errors with proposed formula

fabien.junod

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

error_weekdays_105419.jpg

rob_marshall

@fabien.junod 

 

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