Help with formula column - Date comparison

[Rafael Tognolo]

Hi,

I need help to set up a formula that should display the quarter (Q1, Q2, Q3 and Q4) according to the ticket's due date.

I couldn't find a way to compare the dates and show the respective quarter in the column.

This is the formula that I've tried. I also tried to embed a JQL query, but again, no success.

If

(duedate >= 01-jan-22 and duedate <=31-MAR-22,"{color:red}Q1{color}",
duedate >= 01-apr-22 and duedate <=30-jun-22,"{color:red}Q2{color}",
duedate >= 01-jul-22 and duedate <=31-sep-22,"{color:red}Q3{color}",
duedate >= 01-oct-22 and duedate <=31-dec-22,"{color:red}Q4{ color}")

[Dionathan Lopes {ALM Works} · Accepted solution]

Hello @Rafael Tognolo 

Dionathan from ALM Works here. Welcome to the Community!

If you are using an on-prem version of Structure, please try the following formula code and let me know how it goes:

IF(
duedate >= DATE('2022-01-01') and duedate <= DATE('2022-03-31'), "{color:red}Q1{color}",
duedate >= DATE('2022-03-01') and duedate <= DATE('2022-06-30'), "{color:red}Q2{color}",
duedate >= DATE('2022-07-01') and duedate <= DATE('2022-09-31'), "{color:red}Q3{color}",
duedate >= DATE('2022-10-01') and duedate <= DATE('2022-12-31'), "{color:red}Q4{color}"
)

PS: Make sure to change the Option to Wiki Markup 

You can also refer to this article for more information about Date and Time Functions 

Please let me know if it does help, or if you have any other questions.

Best Regards,
Dionathan

[Rafael Tognolo]

Hello Dionathan,

Thank you. It works perfectly :) 

Best regards,
Rafael

[Craig Nodwell]

Hi @Rafael Tognolo welcome to the community.  Jira has some great date filtering functions.  Have a look at this page.

[Rafael Tognolo]

Hi @Craig Nodwell thank you.
I forgot to mention that the help needed is related to the formula columns in Jira Structure.