Time Elapsed from Issue to Start

[CalvinFold]

I have a request for getting the elapsed time from when an Issue is submitted (in a stand-alone request queue project) to when it was converted to a project or task (in a separate project from the Issue).

So the first trick was getting the start date that the Issue was accepted and converted, which I am calling Work Start Date, since it could be a project or a task. I am doing this currently on an Issue report using:

valueexpression=IF(ISBLANK({resolveTask}.{entryDate})=true,{resolveProject}.{entryDate},{resolveTask}.{entryDate})

This is working, after my usual frustration with fiddly bracket syntax. ;-)

The problem is, I then need to get the number of weekdays between the result from above and the Issue submit date. I've tried a number of combinations like below that do not work:

valueexpression=WEEDAYDIFF({entryDate},(IF(ISBLANK({resolveTask}.{entryDate})=true,{resolveProject}.{entryDate},{resolveTask}.{entryDate})))

Assuming it did not like the nesting, I was trying to put the Work Start Date on a custom form in a custom field so that I could access the result more directly, but on both Issue-level and Project-level custom forms I kept getting Invalid errors, and it seems like Resolved fields may not be supported on these custom forms?

Manager wants this ASAP and I am so close!

Any thoughts?

[AnthonyIm]

If you have the first calculation working the way you'd like it, my recommendation would be to put that on as a calculated field on the custom form (format date) and then just use that field for WEEKDAYDIFF expression.

I'm thinking, maybe the value that is returned from the Work Start Date is not in a date format for the WeekDayDiff one to work

For the calculation, it would be

IF(ISBLANK(Resolve Task.Entry Date),Resolve Project.Entry Date,Resolve Task.Entry Date)

Hope that helps for now.

[CalvinFold]

Anthony,

Yeah, this works fine on an Issue Report:

displayname=Work Start Date

textmode=true

valueexpression=IF(ISBLANK({resolveTask}.{entryDate})=true,{resolveProject}.{entryDate},{resolveTask}.{entryDate})

valueformat=atDate

I assume if atDate works, then the output from the Value Expression is a date.

I tried to do exactly what you suggested—custom field with the above calculation, at the Project level—with no luck, keep getting "Custom Expression Invalid". Perhaps a syntax issue, but I've tried a few variations already. I also tried using the UI to build it, but "resolve" fields aren't in the UI builder.

I tried your version of the formula in a calculated project-level field, and it's as invaid as my own attempts. :-(

If it's syntax, maybe I have the wrong brachets, parenthesis, periods, colons, something?

[AnthonyIm]

So sorry, the calculated field for the custom form needs to be on an issue. A project cannot be converted into a task or project so cannot reference Resolve Project.

I have tested out the calculation and it works, so you shouldn't have any issues.

Now the valueformat is for the end calculation so that doesn't help in the middle of your WEEKDAYDIFF calculation. Since you are using an IF statement, you are no longer in the realm of just Date/Time anymore and I think that might be what is why WEEKDAYDIFF isn't working.

I, personally, cannot get what you want to work without using the calculated field

[CalvinFold]

I think I figured it out; seems the order of the dates makes a difference in WEEKDAYDIFF:

valueexpression=WEEKDAYDIFF({entryDate},IF(ISBLANK({resolveTask}.{entryDate})=true,{resolveProject}.{entryDate},{resolveTask}.{entryDate}))

Didn't think it would make a difference, but apparently the earliest date must go first?

Either that or I accidentally corrected a bracket/parenthesis error when re-creating the expression.

[AnthonyIm]

Hi - I can't get mine to work. What do you have for the valueformat?

[CalvinFold]

I didn't end-up needing a field on a custom form, I did it all directly on the report:

displayname=Elapsed Weekdays

textmode=true

valueexpression=WEEKDAYDIFF({entryDate},IF(ISBLANK({resolveTask}.{entryDate})=true,{resolveProject}.{entryDate},{resolveTask}.{entryDate}))

valueformat=HTML

[AnthonyIm]

OH! I see my mistake. I still had atDate which doesn't make sense as WEEKDAYDIFF would be a number not a date. When I switch to HTML, I get the answer.

#MondayBrain ;)