Creating a Structure formula column: Want to calculate days remaining based on JIRA work days.

[Yvette Nash]

Hello!

I have a structure formula column where I want to show the days remaining between NOW() and the due date on an issue. Trick is I want it calculated based on our JIRA configurations and to include US holidays. Essentially I want remaining business days until the Due date. The calendar set in my structure's general settings has the holidays included. The only code I can get to work though ignores all JIRA settings and is based on 7 days a week (DAYS_BETWEEN(NOW(), dueDate)) so its including weekends and holidays.

Any help is greatly appreciated!

Yvette Nash

[Stepan Kholodov _Tempo_ · Accepted solution]

Hello @Yvette Nash 

At this time, the Formula column doesn't support work calendars. All days are considered, including weekends and public holidays.

You can tailor a formula to exclude Saturdays and Sundays like this:

with start = start_date:
with end = due_date:
with startweek = IF(weekday(start) < 6; weekday(start); 6):
with endweek = IF(weekday(end) < 6; weekday(end); 5):
with week_dif = weeknum(end) - weeknum(start):
with dif = days_between(start, end):
IF start and end: IF week_dif = 0: endweek - startweek + 1
ELSE IF week_dif = 1: 6 - startweek + endweek ELSE IF week_dif > 1: 6 - startweek + endweek + (week_dif-1)*5

Alternatively, if you have the Gantt Charts extension of Structure, then you can see the duration between two dates with the consideration of all defined non-working days. All you need to do is define now() and duedate as Start & Finish dates for scheduling, and select a right work calendar for resources.

Side note: if you need to calculate full days and you don't need hours and minutes, it's better to use the today() function instead of now() - the latter can trigger recalculation of the structure often since the now() value will be changing all the time.

I hope this helps. If you have more questions about Structure, please reach out to us directly at our support portal.

Best regards,
Stepan
Tempo (the Structure app vendor)

[Yvette Nash]

I do have the Gantt extension and due to your suggestion discovered a whole slew of new code I can use in my formulas! I had no idea there were so many Gantt specific functions. Setting up a slice to use today() as the start date works perfect! Thank you so much!!