Structure Formula - Filter Worklogs by Date Range

Question

Hi there, First time posting. Not a dev. Extreme newb to expression formula. I have a structure with issues and subtasks that have work logs against them. I would like to sum the work logged between a certain date range using the Jira Structure formula column. This unexpectedly returns values but the .filter operator is wrong "< sprintStart": /* Displays work logged between a specified date range. */ WITH sprintStart = date("27/03/2023") : WITH sprintEnd = date("11/04/2023") : WITH workLogged = worklogs .FILTER( $.startDate < sprintStart ) .FILTER( $.startDate < sprintEnd ) .timeSpent.SUM() : jira_days(workLogged) This however, with the correct operator returns nothing "> sprintStart": WITH workLogged = worklogs .FILTER( $.startDate > sprintStart ) .FILTER( $.startDate < sprintEnd ) .timeSpent.SUM() : jira_days(workLogged) Any help would be great even a whole new direction would be welcome. I know there is a Work Logged column available but I have to use the formula column in this instance. Cheers, James

James Roy_4py2oo · Answer

Hi @David Niro ,

Thanks for getting back to me. I ended up using the .Filter a little differently. Perhaps trying to use two was causing an issue but the following seemed to work for me.

WITH endDate =
DATETIME("9/May/2023 12:00 AM", "en_AU", "Australia/Melbourne")+86400000 : /*end date plus 1 day*/

WITH startDate =
endDate - (86400000 * 13) : /*end date minus 13 days*/

WITH workLogged =
worklogs
.FILTER( $.startDate > startDate AND $.startDate < endDate ) 
.timeSpent.SUM() :

CEILING(jira_days(workLogged),3)

David Niro · Answer

Hello @James Roy ,

Welcome to the Community!

Apologies for the delay.

I believe it's the Date format that is causing a problem here.  It looks like you are using dd/mm/yyyy.  Try mm/dd/yyyy, it worked for me in my testing.

Please let me know if it helps!

Best,
David