Expand my Community achievements bar.

Calculate length of time project has been in status

Avatar

Level 7

How might you calculate the length of time a project has been in a specific status? Specifically I'm trying to build a report that flags projects that have been on hold for several weeks/months.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

2 Replies

Avatar

Community Advisor

I don't know that there will be a way to do this without either a calculated field on the project, or a journal entry report. Both kind of have their own problems.

 

Here's a sample custom calculated field calculation:

 

 

 

IF(Status='KEY',IF(ISBLANK({DE:Custom Field}),$$NOW,{DE:Custom Field}),{DE:Custom Field})

 

 

 

(KEY is whatever status you're looking for's 3-character code)

 

- Fields like this will capture the first time, which is great unless you're looking for more than one time.

- You could set it up to capture more than one time, but then you can't do any calculations, you just end up with a string of dates displayed, per project.

- you can't go "back in time". The calculated field starts capturing data as soon as you put it in, not before.

 

For a journal entry report, you have to be very clear about what you're looking for (and also good at interpreting journal entry reports) Here is a sample filter to at least get you started -- you'll want to add to it. Just switch it back to standard mode once you copy/paste it into your report:

 

 

 

opTaskID_Mod=isblank
fieldName=status
fieldName_Mod=cieq
newTextVal=ONH
newTextVal_Mod=cieq
changeType=E
changeType_Mod=in
projectID_Mod=notblank
taskID_Mod=isblank

 

 

 

 

The main down side is that it isn't connected to your standard project report -- it's a whole report in and of itself. On the other hand, you can go back in time quite a ways.

 

Avatar

Level 7

I suspected this might not be easy. I also suspected you might be the one to chime in  Thank you for the insights!

That all said, I've created an idea folks can upvote: New field: Duration in current status - Adobe Experience League Community - 606050.