Calculated Date Field that Skips Holidays

[DerekSu1]

I am using this calculation on a date field to calculate due dates. ADDWEEKDAYS($$TODAY,4) How can I modify this to exclude holidays so that they are not counted as business days in the calculation? My instance of Workfront does not have a holiday library so I would need to list them manually in the calculation and then likely update every year I would assume. Thanks in advance.

[Sven-iX]

Hi @DerekSu1 

The "schedules" are exactly for that - letting WF know which are work-days and non-work-days. Would definitely recommend using those. 

Due dates are calculated by Workfront based on predecessors and constraints - it's out of box and I would recommend looking at your use case to see how you can translate this into a task whose planned completion date is the "due date" you need. 

Calculated expressions are not really sub-routines or macros - they're more like Excel formulas in that you can chain expressions etc but it can get unwieldy quickly. Moreover, calculated fields get re-evaluated when the object custom data is updated (eg when you edit custom form fields) so your expression ADDWEEKDAYS($$TODAY,4)  would be a due date that is always 4 days away... 

Can you share more about your use case and maybe there's a more straight-forward solution? 

[DerekSu1]

Thank you for responding.  I have a custom form where users are submitting tasks requests to a team.  The form has options for how quickly the user needs to the task to be completed.  I am trying to populate a field within that form that shows the user when their request will be completed based on the turnaround time/priority (4 days is one) that is chosen.  I would like that due date field to take into account holidays in India so that that users get the correct date when selecting the turnaround time/priority of the task.  Appreciate the help!

[Sven-iX]

I don't see how to do that with a calculated field. 
But - maybe you can use priority field and then an external lookup field that takes this priority value and uses Fusion to to the lookup - which you can have looking at the schedule, or even hard-coding the holidays. 

[DerekSu1]

Okay, thanks for checking into it.

[RowvillBh1]

Create three calculated fields. Think of the first two as supporting fields :

Calculate "4th Day" - ADDWEEKDAYS($$TODAY,4)

Calculate working day difference between today and 4th day "Diff" - WORKMINUTESDIFF($$TODAY, ADDWEEKDAYS($$TODAY,4))

Calculate "Due Date" - IF(DE:Diff>4,ADDWEEKDAYS($$TODAY,SUM(DE:Diff,SUB(4,DE:Diff))),ADDWEEKDAYS($$TODAY,4))

The field "Due Date" would be the one you use to get the accurate 4th day considering holidays. This would only work if the default schedule on your Workfront instance includes the holidays you want to exclude.

[kautuk_sahni]

@DerekSu1 Just checking in — were you able to resolve your issue?
We’d love to hear how things worked out. If the suggestions above helped, marking a response as correct can guide others with similar questions. And if you found another solution, feel free to share it — your insights could really benefit the community. Thanks again for being part of the conversation!