Calculate length of time project has been in status

Question

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.

skyehansen · Answer

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded