Expand my Community achievements bar.

Time Elapsed from Issue to Start

Avatar

Level 10

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?

Topics

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

7 Replies

Avatar

Level 10

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.

Avatar

Level 10

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?

Avatar

Level 10

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

0694X00000FQysYQAT.png

0694X00000FQyuUQAT.png

Avatar

Level 10

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.

Avatar

Level 10

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

Avatar

Level 10

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

Avatar

Level 10

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 ;)