Task Status Report

[kcasaert]

hello! we are wondering if there is a way that you can pull a report showing how long (duration) a task is in each task status. We are wanting to show how long a task is in review vs development - any help appreciated! 

[Richard_Le_]

Hi,

This is possible to achieve, but it will involve adding calculated fields to your projects that capture a timestamp of when the project was set to a particular status. You would then be able to measure the difference between these timestamps and work out the duration that the project was in that particular status. 

See this previous post here for a more detailed explanation and some example textmode you can use.

Best Regards,

Rich.

[Matt_McC]

We accomplished something similar by creating calculated fields on the task to timestamp the changes. This is not quite what you asked (duration) but you may be able to modify and split the calculation into separate fields to get what you need.

It required two custom fields. 

1 DE:Current Status

IF({status}="NEW","New",IF({status}="INP","In Progress", IF({status}="AWF","Awaiting Feedback", IF({status}="CPL","Complete", IF({status}="ONH","On Hold", IF({status}="COM","Complete",{status})))))) 

Create this first, save and close the form before adding the next calculated field..

2 DE:Status History

LEFT(IF(LEFT({DE:Status History},LEN(IF(ISBLANK({DE:Current Status}),"-",{DE:Current Status})))={DE:Current Status},{DE:Status History},CONCAT(IF(ISBLANK({DE:Current Status}),"-",{DE:Current Status})," (",$$NOW,") | ",{DE:Status History})),2000)

Create the field (status history) without the calculation first (save and close the form) then reopen the form to add the calculation.

EXAMPLE OUTPUT (latest status first)

Complete (3/7/23) | In Progress (3/2/23) | New (3/2/23) |

I hope this helps.

Bonus content:

I am often frustrated by the by the fact I cannot order by status in a way that makes sense. So I have this calculated field that allows me to order the status based on an assigned numerical code. It goes up in 10s to allow me to add things easily.

3. DE:Sort By Status

IF({status}="INP",30,IF({status}="NEW",10,IF({status}="CPL",40,IF({status}="AWF",20,IF({status}="ONH",50,IF({status}="BQC",60))))))

Sort Order:

10 New

20 Awaiting Feedback

30 In Progress

40 Complete

50 On Hold

60 Cancelled

[Seanna_Hdz]

I agree with the timestamp calculated field to capture the date of the status change. You could then use a calculation on a report to display the DATEDIFF between each of the timestamps. 

For example, if the timestamp captured for the Review status is 03/01/2024 and the timestamp for the Development status is 03/15/2024, use the following calculation in text mode: 

valueexpression=DATEDIFF({DE:Timestamp Dev Status},{DE:Timestamp Rev Status})

If the need is to see just week days between the two dates, use WEEKDAYDIFF in place of DATEDIFF.