Trying to create calculated field to measure time in a specific Issue status

[knacrelli]

I've looked through the posts here, but don't seem to be finding anything that's working so far. Hoping someone can point me in the right direction since I'm not a Workfront expert.

NEED: To be able to measure the time spent in a specific status on an Issue

DONE SO FAR:

- I created a 'timestamp' field that captures when the status is changed to the one I need (NFU). Here is the expression I used, which seems to correctly put the Date/Time in the field once an Issue changes to that status:

IF({status}='NFU',IF(ISBLANK({DE:Status Timestamp Assignments Requested}),$$NOW,{DE:Status Timestamp Assignments Requested}),{DE:Status Timestamp Assignments Requested})

CAN'T FIGURE OUT:

- Now I need a calculated field that shows the amount of time elapsed since the timestamp above and 'now' to show how long the issue has been in that specific status. I'm not sure where to start on this, so any suggestions would be great!

Update: I tried this expression but it's returning "0"...the format for the field is 'Number'...not sure if that's correct?

WORKMINUTESDIFF({DE:Status Timestamp Assignments Requested},$$TODAY)

Thanks in advance

[Sven-atClient]

HI @knacrelli 

I haven't tested but I think it's because the calc field Status Timestamp Assignments Requested is a string (all calc fields only have string values) and the function WORKMINUTESDIFF is comparing date objects).

See if you can first convert the calc field to a date object with DATE().

[Sven-atClient]

Hi @knacrelli 

I was wrong: The DIFF functions take strings which are then parsed to date objects, so what you're doing is fine. 

Note though to get positive deltas, use $$TODAY or $$NOW as the first argument. 

@Lawson02 is 100% correct: to show this difference, you'll need to keep updating the issue - so I would avoid putting the difference calculation in a field (Like Lawson said, put that calculation in the report column).

Now - why do you keep getting 0 ? 

Looks like the calc field, when setting it to $$TODAY or $$NOW only captures the DATE, not the time, and it'll use UTC date format. (see docs)

So you have to create a proper date/time string - both in the field where you set the time the status changed to NFU and when you compare that field to current time. It's ugly but works. In the screenshot you'll notice I "reset" the field to a dash (because setting to blank doesn't change the value) when you change the status to NEW. Depending on your use case you may want to do that - or not. 

Last: I added an "ADDDAYS -4" because WORKMINUTES only get calculated during the worktime and it's after hours here  

If you wanted the code

    IF(
        {status}="INP" && (ISBLANK({DE:test status NEW})||{DE:test status NEW}="-"),
        YEAR($$NOW)+"/"+IF(MONTH($$NOW)<10,"0","")+MONTH($$NOW)+"/"+IF(DAYOFMONTH($$NOW)<10,"0","")+DAYOFMONTH($$NOW)+" "+IF(HOUR($$NOW)<10,"0","")+HOUR($$NOW)+":"+IF(MINUTE($$NOW)<10,"0","")+MINUTE($$NOW)+":"+IF(SECOND($$NOW)<10,"0","")+SECOND($$NOW),
        IF(
            {status}="NEW",
            "-",
            {DE:test status NEW}
        )
    )

and 

WORKMINUTESDIFF(YEAR($$NOW)+"/"+IF(MONTH($$NOW)<10,"0","")+MONTH($$NOW)+"/"+IF(DAYOFMONTH($$NOW)<10,"0","")+DAYOFMONTH($$NOW)+" "+IF(HOUR($$NOW)<10,"0","")+HOUR($$NOW)+":"+IF(MINUTE($$NOW)<10,"0","")+MINUTE($$NOW)+":"+IF(SECOND($$NOW)<10,"0","")+SECOND($$NOW),ADDDAYS({DE:test status NEW},-0.4))

[Lawson02]

Try DATEDIFF($$NOW, {DE:Status Timestamp Assignments Requested}).

Unless this is showed within a report the issue with a field like this is that it doesn't auto update $$NOW unless you recalculate, update the issue, or use Fusion to create an update once per day.