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

Question

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

Billy Kelly · Answer

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")))))

David Niro · Answer

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 · Answer

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)