Calculate days between Due date and start date

[Shindlay Nath]

I am trying to automate a value calculation on difference between due date and start date to a Custom Fields.

Trigger

when a start date is set on a card with a due date

Action

set custom field

Workdays

to

{{cardduedateyeardaynumber}-{cardstartdateyeardaynumber}}

When I try this, the activity log says 'Cleared Custom Field "Workdays" in card'.

How do I perform this calculation, tried looking at an old quest from 2021, but it did not help at all.

[Mike - Dreamsuite Consulting · Accepted solution]

Hi @Shindlay Nath 

Change your automation action to:

{{cardduedate~X} - {cardstartdate~X}) /86400

This article will help you a lot:

https://support.atlassian.com/trello/docs/arithmetic-and-formatting-in-date-variables/ 

[Chadwin]

Hi Shindlay,

From what you've described with trying to calculate the difference between Start Date and Due Date into a field, the Amazing Fields power-up can help.

With Amazing Fields, you can use formulas calculate field values instead of automation. Date subtraction works natively, so you can calculate the day difference between the two dates and have it update automatically whenever either date changes.

For example, for your use case, you'd add your source fields {Start date} and {Due Date} as well as your calculated field {Difference between start date to due date}. For the {Difference between start date to due date} field, you would add the below formula to calculate the difference:

(YEAR ({Due date}) > 1970 && YEAR ({Start date}) > 1970) ? ( {Due date} - {Start date} ) / 86400000 : 0

You can install Amazing Fields from the Trello Power-Ups directory and set everything up in just a few clicks. Read more here: https://trello.com/power-ups/60e068efb294647187bbe4f5/amazing-fields

Hope this helps! Reach out to the Amazing Fields team for more support if needed at support@amazingpowerups.com.

[Shindlay Nath]

Hello Chadwin,

Thank you for the answer, I did try the Powe-Ups and it did function as I intended. But I do have some questions regarding this.

Would it be possible to use the date selection within the Trello card instead of adding new date selection fields? 

How would I sort my card in the list based on {Difference between start date to due date} field as it is not visible in automation ?

Thanks for providing me with an answer, hope fully you could answer my queries.

Regards
Shindlay

[Chadwin]

Hi Shindlay,

You won’t be able to use Trello’s native date fields directly, since Power-Ups can’t read those built-in fields for calculations.

Instead, you can wrap your Amazing Fields fields with Trello custom fields. Just go into the Amazing Fields field settings and create a new custom field from the “Custom Field Wrapper” section. Read more here: https://docs.amazingpowerups.com/article/13-custom-field-integration

This article also takes you through using a browser extension to hide your custom fields, since you'll have two sets of fields (custom fields and Amazing Fields' fields) and you're only using them to sort your list. 

With this setup, you can then sort your cards based on the calculated “difference between start and due date” field using the new custom field and Trello’s built-in sorting options, rather than relying on automations.

Hope this helps!