How to calculate calendar days between 2 Jira date fields in Structure

[Manali Butey]

Aim: To set fixed duration on gantt chart using manually set start and due dates by calculating the duration between them excluding weekend and if possible public holidays

I have:

• 2 fields - start and due
• A formula column (fixed duration cal) - (due - start) with Options set as 'Duration'

The formula column returns in Jira text duration format the number of working dates between the two dates. . When I copy paste this formula into the Fixed Duration section under Scheduling under Gantt Config with format as 'Text Duration' it returns a scheduling error.

After a couple of tries I realised that it accepts the day_between(start,due) and I am able to successful set fixed duration on all my tasks in the Gantt chart. However, this is not the desired outcome because the days_between calculation is inclusive of weekend so the calculated duration of tasks is wrong.

Now to get around this I started looking at the DURATION function and created another formula column- (((DURATION(fixed duration cal)/1000)/60)/60)/24
I am trying get the fixed duration cal column that returns the CORRECT duration of tasks in Jira text format (which I assume is text) as input to DURATION function (which accepts text as input) and outputs the duration in ms and then perform some math (/1000)/60)/60) on it to get the number of hours.
However, this has failed majorly cause the DURATION function is accepting fixed duration cal col as input but the outut numbers are bizarre when compared to an input such as "23d"

Why is it doing that? What am I missing?

Next step: If this formula works I will use it in the Fixed Duration section of my Gantt chart to auto set duration on all tasks in the Gantt chart.
Formula - ((((DURATION(fixed duration cal)/1000)/60)/60)/24)*8 

[alexeypegov · Accepted solution]

Hello Manali!

This is Alexey from ALM Works.

Regarding Scheduling error, it might happen that you have encounter a bug or otherwise something is wrong, in order to understand that, please contact our support. 

Regarding formula itself, the idea is to find out number of weeks between two dates and then subtract corresponding number of weekends from the total days:

WITH raw_days = days_between(start, finish) + 1 :
WITH weeks = floor((raw_days + weekday(start)) / 7) : 
days - weeks * 2

You will need to select Days format for Duration attribute in Gantt in this case.

Please, pay attention that holidays should be excluded separately, also both start & finish should be regular workdays for this formula to work.

Hope it helps!

[Manali Butey]

Hi @alexeypegov 

Thanks for the prompt response. I am trying this out now. Guess the last line in the formula is 

raw_days - weeks * 2

[alexeypegov]

Yes, it's 'raw_days'! Sorry for confusion.

[Mykola Bilenko]

@alexeypegov 

Hi, Alexey. How can looks formula which count amount of vacation days for each month?

My issues consists "DateStart" and "DateEnd" dates expressed in custom fields. And I have cases when vacations are started in one month and repeated in the next one.

So, how can I calculate monthly "raw_days"? Thnx.