Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,298,803
Community Members
 
Community Events
165
Community Groups

Calculate days remaining

Pulling my hair out trying to get a custom field to give me the days remaining. Why is this so hard!?

3 answers

1 accepted

0 votes
Answer accepted

Hi @Hannah Morgan ,

 

After all of this I found a simple fix using rules! I don't know if this is a new feature, but it works!

"when a due date between 60 and 90 days from now is set on a card, set custom field "Due Date Value" to "2", set custom field "Priority" to "({{%Time Sensitivity}} + {{%Work-a-round}} + {{%Compliance}} + {{%Organizational Impact}} + {{%Due Date Value}}*5", and sort the list by custom field "Priority" descending"

YAY!!!

Not sure if you have tried it, but you may want to give Amazing Fields a try.  https://trello.com/power-ups/60e068efb294647187bbe4f5

It can share data with custom fields, allows formula calculations, and could allow you to style the color of the date based upon how close the due date is.  I know several people are using it for exactly this type of thing.

It wouldn't update the calclulation automatically each day but depending upon what you want there you may be able to do that with the styling rules.

@allen good idea! I looked into Amazing Fields before and I don't remember it having the calculated fields functionality. Is this new?

0 votes
Hannah Morgan Community Leader May 18, 2022

@Mairi Nixon welcome to the community :) Are you able to elaborate a bit? Are you wanting to calculate how many days between now and the due date? Are you able to share what you've set up so far?

Hi @Hannah Morgan !

I have a form which asks users to provide the Required Date/Due Date. The date is populated in a custom field, which I use to calculate a priority score based on days remaining. I tried setting up a number custom field for this using "set custom field "Date Diff" to "{{%required by}-date}"" but that did nothing. 

help!

Hannah Morgan Community Leader May 18, 2022

Got it! Thanks for clarifying. Butler can execute calculations, but those calculations only work with custom fields that are number types. It's not like excel which allows you to create calculations with date fields. See here under "Custom field arithmetic".

So, with that being said, what you can do is convert your date fields into number fields. You can either convert them to unix epochs, or convert them into the year date number.  So the idea would be to create two number fields - one to hold the required by date, and another to hold today's date. You can use butler to populate these fields into the number format when the required by date is set, and then have one last action to subtract those number fields and populate the difference into your "Date Diff" number field.

See below documentation for this:
https://help.trello.com/article/1157-variables#time-and-date-variables
https://help.trello.com/article/1159-arithmetic-in-date-variables
https://momentjs.com/docs/#/displaying/

You can probably tell already that Trello's not really designed to do anything beyond a basic calculation, so you might also want to explore the Smart Fields power up.

Hi @Hannah Morgan 

Thank you! I will try this and get back to you if I run into trouble. I actually have been using Smart Fields, but there are two problems with the App....

1. It doesn't update each day so the priority score will not change based on days remaining after the initial calculation

2. It doesn't mirror custom fields so you have to manually enter in all values populated by the form to custom fields at each submission (time-consuming).

I'll give your route a go and see where it gets me :) MUCH APPRECIATED!

I fiddled a bit and couldn't get it to work. This is where I'm at...

CF 1: required by (date format)

auto populated

CF 2: today (date format)

populated with todays date on ticket creation

CF 3: convert 1 (number format)

set custom field "convert 1" to "{required by~X}"

CF 4: convert 2(number format)

set custom field "convert 2" to "{today ~X}"

CF 5: Date Diff (number format)

set custom field "Date Diff" to "{{%convert 1}-{{%convert 2}"

The conversion doesn't seem to be doing anything. Am I missing a step?

Hannah Morgan Community Leader May 19, 2022

@Mairi Nixon For the converted fields, you'll need to use the custom field variable {{%required by}~X} and {{%today}~X}. The last one to calculate the difference, will give the number of days, but it will be in epoch unix format. I'm not sure if there's a way to convert that to human time using butler. 

@Hannah Morgan so close! I was able to successfully convert the two dates to epoch time, but the date diff field won't run the subtraction :/

I have tried:

set custom field "Date Diff" to "{{%convert}} - {{%ct}}"

set custom field "Date Diff" to "({{%convert}} - {{%ct}})"

None of the above work, which is weird because it's a simple subtraction expression...

Hannah Morgan Community Leader May 21, 2022

@Mairi Nixon the first command is the correct one (without the brackets). It should work if the "Date Diff", and both the convert fields are all number fields (well it works for me anyway). Are you able to share the full command you created? Can you see anything in the command logs? Maybe also try setting the action to have the rule output to a comment or something rather than the custom field so you can see what butler is trying to put into the field (i.e. it might be trying to add non-numerical characters).

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Trello

Taco Tuesday: New years' resolutions with Trello

Congratulations to @Laura Holton , our latest winner of Taco Tuesday! And thanks to @Kristján Geir Mathiesen for sharing the picture of Taco having fun with his new friend  B...

2,384 views 28 41
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you