Multiple date values in Structure formula column

[Poojaa Sudhakaran]

Hello there,

I'm using Structure's formula column to display all the due dates belonging to "In Progress" sub-task at story level. I'm able to achieve this using aggregate functions in structure but I'm facing issues in the format in which it is displayed - PFB, screenshot.

Formula used:

IF(itemtype = 'issue', JOIN#CHILDREN#distinct{IF(status="In Progress",duedate)})

 and Formula column configuration:

 

Could you help me resolve this?

Thanks,

Poojaa

[Philip Heijkoop _ALM Works_ · Accepted solution]

Hi Poojaa,

Phil here, from ALM Works (makers of Structure). That's an interesting little conundrum you've got, but thankfully I think we have an answer. 

At present you're performing a join operation on the child date values, and then asking the formula column to format the values for you. What we're going to do is flip that operation. As you can see in our function reference page, we have a few datetime format functions. Let's try the following formula, with the column's formatting set back to general.

IF(itemtype = 'issue', JOIN#CHILDREN#distinct{IF(status="In Progress",format_datetime(duedate, "dd/MM/yy"))})

Naturally, you are more than welcome to tweak the exact format, as you need it. Since formatting the datetime value to a pretty text piece would work the same for each one, it also preserves the distinct requirement.

Hope that helps! Please let me know if it didn't or you have any followup questions.

Phil Heijkoop

Lead Solutions Engineer

ALM Works 

[Poojaa Sudhakaran]

Thanks Phil ! This worked 🙂

[Marc Dahinden]

Hi Phil
Your post helped me a lot.
I used your coding format_datetime(duedate, "dd/MM/yy") to display the due date in a specific format.

I extended the code to format_datetime(duedate, "dd/MM/yy hh:mm") what issues me a time as am or pm. I prefer the time as 00:00 till 23:59.

How do I code this?

Thanks

Marc

[David Niro]

Hi @Marc Dahinden ,

Rather than "hh:mm", you will want to use "HH:mm"

Hope this helps!

Best,
David

[Rajal Shah]

hey @David Niro - how would you dump the list of all date (say in the Due Date field) in a Jira structure formula column, so that except for the current one all previous due dates are unique and have a strikethrough formatting, so it gives a visual of changed dates..

Here is a visual I am looking for.

1/13/2025

3/21/2025

4/5/2025