Using date result formula for next field calculation

[Mohammed Younos]

Hey Atlassian Community,

I have an issue with trying to work out how to get a date in one column on a structure to be calculated and then use this value in another field.

My overall target is to: show a date in a newly created field "sprint due date", based of another field "target_sprint", then show the health in another field based of the sprint due date vs. time left.

My first column in the Structure, reads the value of the sprint name and converts this to a date value:

IF type = "epic" or type = "story":

    IF target_sprint = "Sprint.23.3.2":
        DATE("08/18/2023")

The field is then populated with this date, correctly when the "target_sprint" is equal to that name. 

I then have another field, which is a duplicate of the following, Wiki markup example 3:

https://wiki.almworks.com/documentation/structure/8.3/data-center-and-server/wiki-markup-in-formula-columns-155812076.html

When I use this field and change the following line from:

with diff = FLOOR((due_date - (TODAY() + WORK_TIME_TO_CALENDAR_TIME(sum {remaining}))) / 86400000):

To:

with diff = FLOOR((sprint_due_date - (TODAY() + LOGGED_TIME_TO_CALENDAR_TIME(sum {remaining}))) / 86400000):

(due_date is replaced by my column name sprint_due_date).

In my health column, I have edited the formula, by selecting "sprint_due_date" variable, highlighting the column to extract this from "sprint due date (formula)".

Has anyone any experience of doing this and where am I going wrong?  It might also be bad formatting of the date, something as simple as that.  Please ask questions, as I may not have explained the issue in enough details.

[Mohammed Younos]

Actually, just found the answer!  Might be worth others noting this, but also I am not sure how it correlates (syncs).

On the Health column, I select the variable "sprint_due_date", then select it as a Formula and click the settings icon next to the Formula field.  

The dialogue box expands to show the formula to calculate the "sprint_due_date"

I simply updated this with my new code (first snippet above) and it worked.

The only issue is the synchronisation:  The code that originally appeared in this dialogue box was my previous attempts and did not work correctly.

Although I had updated the field, the code within this dialogue was previous version.

Not sure how this was populated originally, or if it takes a snapshot when setting up.

This is no longer an issue, just a nice to know :-)