Microsoft Flow – formula to calculate the due date excluding weekends

In Blog by Ivan Wilson

We’ve been doing a lot of work recently with Flow, Microsoft’s latest workflow product for Office 365. Here’s a formula you might find useful if you need to calculate a due date for a task, but factor in weekends. For example, if your start date is a Friday and the task duration is 2 days, then you want the due date set to Tuesday, not Sunday.

To use this formula, you will need to add three Initialize Variable actions – startDate (string), duration (integer) and dueDate. Specify your start date using ISO notation, such as 2017-10-27. Set duration to the number of work days the task will take. Set the final Initialize Variable action to the formula below.

if(equals(dayOfWeek(adddays(if(equals(dayOfWeek(variables('startdate')),6),addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))),6), addDays(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5))),2), if(equals(dayOfWeek(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))),0), adddays(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5))),1), adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))))