Structure formula for duration between two date fields in a given month

[Billy Kelly]

Hello, I've built a structure with a number of columns to help track work based on engineering tickets during a month so we know what effort and work has been done in given a month 

I have a column for the number of working days in the month using Jira fields Start_date and Due_Date but now I need to extend this to a column for March then a column for April, etc to show the total of days worked in that month. This should give us a worth idea of how many days were worked on the ticket without the need for time tracking. 

The formula below is my attempt to only use tickets that start date or due date is in March. I get a type error that a number could not be converted to numeric format. I'm open to a better way of during this as well 

with march = startdate >= "2022-03-01" :
with marchend = duedate <= "2022-03-30" :
WITH raw_days = days_between(march, marchend) + 1 :
WITH weeks = floor((raw_days + weekday(march)) / 7) :
raw_days - weeks * 2

[David Niro]

Hello @Billy Kelly ,

David from ALM Works here.

Based on what you've described, it sounds like Formulas are not the solution you would need in order to accomplish your goals.

I know you were trying to avoid it, but our suggestion would be to use Time Tracking, or to check the Marketplace for other Time & Team Management Apps.

Best,
David

[Billy Kelly]

Thanks - I have it working in Google sheets so I thought as structure has the same functions, max, min, and End_OF_MONTH it should work but it doesn't like the arguments are the same 

This would be a colum for April 

IF(CALENDAR_DAYS
(MAX(startdate,date("4/1/2022")),
MIN(enddate,END_OF_MONTH(date("4/1/2022"))))<0,
0,
CALENDAR_DAYS(MAX(startdate,date("4/1/2022")),
MIN(enddate,END_OF_MONTH(date("4/1/2022")))))

[Billy Kelly]

So I have two different approaches to tackle this which picks up when a start date or due date is in the right range  shown below but the issue I will have some start dates in March and end dates in April, whereas I only want to calculate the March day 

with march = (startdate) >= date("2022-03-01") :

with marchend = (duedate) <= date("2022-03-31") :

WITH raw_days = days_between(startdate, duedate) + 1 :

WITH weeks = floor((raw_days + weekday(march)) / 7) :

IF (march>0 OR marchend>0; raw_days - weeks * 2)

WITH startz = JQL {"Start Date[Date]" >= 2022-03-01 AND "Start Date[Date]" <= 2022-03-31 } :

WITH endz = JQL {"Due Date[Date]" >= 2022-03-01 AND "Due Date[Date]" <= 2022-03-31 } :

WITH raw_days = days_between(start_date,Due_date) + 1 :

WITH weeks = floor((raw_days + weekday(start_date)) / 7) :

IF (startz>0; raw_days - weeks * 2)