Breadcrumbs

Days between dates

📚

This template displays the days elapsed between two dates selected in the date fields of your choice.

If either field does not have a value, “None” will be displayed.

For the parameters From and To, pick the Date fields that should be compared.

Configuration

To use the Days between dates template, simply select it from the template grid. Filter by SLA, aging & compliance timelines to find it faster.

Parameters

To complete the configuration, select values for all parameters.

image-20260617-151810.png

Edit formula mode

If you click on Edit formula you can see the formula field in the Expression Parser. You can now tweak the expression to create a custom formula field based on this template.

Expression

General expression:

%{%{issue.cfFFFFF} != null AND %{issue.cfTTTTT} != null? abs(datePart({issue.cfTTTTT},RUN_AS_LOCAL) - datePart({issue.cfFFFFF},RUN_AS_LOCAL)) : null}

FFFFF and TTTTT are the IDs of the custom field you chose for the “From” and “To” parameter.

Display as

Duration

Formatting style

short

Used parser functions

The links lead to the JWTC documentation because the parser functions are shared functionalities.

Details

1. What does the expression do?

The expression calculates the absolute difference (the total number of days) between two date fields (a "From" date and a "To" date). If either of these fields is empty, the expression returns "None" (null) instead of an error. The result is typically displayed as a duration (e.g., "5d").

2. Step-by-step breakdown

The expression uses the following logic:
%{%{issue.cfFFFFF} != null AND %{issue.cfTTTTT} != null ? abs(datePart({issue.cfTTTTT},RUN_AS_LOCAL) - datePart({issue.cfFFFFF},RUN_AS_LOCAL)) : null}

  • %{issue.cfFFFFF} != null AND %{issue.cfTTTTT} != null: This is a conditional check. It looks at the "From" field (cfFFFFF) and the "To" field (cfTTTTT). It asks: "Do both of these work item fields have a value?"

  • ? ... : null: This is a Ternary Operator (an "if-then-else" shortcut). If the condition above is true, it runs the calculation after the ?. If it is false (one or both fields are empty), it returns null.

  • datePart({issue.cf...}, RUN_AS_LOCAL): This function extracts the date component from the field. Using RUN_AS_LOCAL ensures the date is interpreted based on the local time zone settings of the system/user rather than UTC.

  • abs(...): This stands for Absolute Value. It ensures the result is always a positive number. For example, if the "To" date is actually earlier than the "From" date, it will still show the number of days as a positive difference rather than a negative one.

  • - (Subtraction): By subtracting the "From" date part from the "To" date part, the parser calculates the numerical difference in time, which is then formatted as days.

3. Examples

  • Scenario A (Standard):

    • From Date: 2024-06-01

    • To Date: 2024-06-10

    • Result: 9 (Displayed as "9d")

  • Scenario B (Missing Data):

    • From Date: 2024-06-01

    • To Date: Empty

    • Result: None

  • Scenario C (Reverse Dates):

    • From Date: 2024-06-10

    • To Date: 2024-06-01

    • Result: 9 (Because of the abs function, it remains positive)

4. Real-life use cases

  • SLA Tracking: Calculate how many days passed between a work item being "Created" and "Resolved" to monitor team performance.

  • Project Management: Measure the duration of a specific phase by calculating the days between a "Start Date" and an "End Date" custom field.

  • Aging Reports: Determine the age of a work item by comparing the "Created" date to a "Target Date" to see how far apart they are.

  • Approval Cycles: Track how long a work item stayed in a "Pending Approval" state by comparing the date it entered that state versus the date it was approved.