Calculate days remaining

[Mairi Nixon]

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

[Mairi Nixon · Accepted solution]

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!!!

[Allen -Amazing PowerUps-]

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.

[Mairi Nixon]

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

[Hannah Morgan]

@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?

[Mairi Nixon]

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]

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.

[Mairi Nixon]

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!

[Mairi Nixon]

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]

@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. 

[Mairi Nixon]

@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]

@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).

[Mairi Nixon]

-