Calculating Difference Between Two Dates (HELP!)

[LadellFu]

Hi, We're trying to calculate the difference between 2 dates (requested delivery date & actual completion date) as a column in a report. The "requested delivery date" is a custom date and "actual completion" is out of the box. We're using the following script in text mode for the column: displayname=Overage textmode=true valueexpression=WEEKDAYDIFF({ Requested Delivery/Launch Date },{actualCompletionDate}) valueformat=HTML However, it's not working with the custom date field. I did try another custom date field that did not have a "/" and it still did not work. When I replace the custom field name with an out of the box name, it does work correctly. Does anyone know how we may be able to make this work properly? Thank you!

[DavidCo3]

HI Ladell, Perhaps try a DE: before the custom field name. e.g. displayname=Overage textmode=true valueexpression=WEEKDAYDIFF({DE: Requested Delivery/Launch Date },{actualCompletionDate}) valueformat=HTML Having said that, there could still be an issue to have a forward slash (/) in the custom field name. Let us know how you go!

[LadellFu]

THANKS SO MUCH!!! We've been trying to do this for two weeks. It works perfectly now!

[Doug_Den_Hoed__AtAppStore]

Brilliant, David. Adding the DE: crossed my mind too; and Ladell, I wish I'd thought to mention it -- luckily, with David's location, you get nearly full global coverage! On the note to self front, I'm going to make it a (stronger) habit to use the {DE:xxx} pattern going forward (in addition to my "don't use special characters in parameter name" rule of thumb), but ESPECIALLY when such a character is involved (e.g. {DE:xxx/yyy}) as appears to have been the case. Regards, Doug

[BenettaPe]

I needed this last week - I am going to store this for when I forget AGAIN how to calculate the difference between two dates. OMG. Thank you. Benetta Perry APS

[BenettaPe]

Uggh, I think I got too excited to quickly. I have a task " Call to Client". I have a task "Go Live". Now I want to calculate the time between these two. They both have an Actual Completion date. But How do I distinguish it in a column? What will the API Mode text look like? Benetta Perry APS

[ChrisCo2]

I have the same question as Benetta. If I'm trying to use the following syntax, how do I make sure the "entry date" is identified as the correct one? I think I want to make sure it's the Entry Date of the Project. displayname=Turnaround days requested textmode=true valueexpression=WEEKDAYDIFF({entryDate},{DE:Requested Delivery Date}) valueformat=HTML Chris Connors IGM Financial

[EXP_-_DustinMa]

Hey Benetta and Chris, In order to compare the two, you'll want to use conditional formatting, not quite a value expression. Here's the link to our help article that goes over the process: https://support.workfront.com/hc/en-us/articles/222425988-Comparing-Fields-in-Conditional-Formatting Thanks! (Also, was out on vacation, hope all was well while I was gone!) Dustin Martin Assigned Support Engineer Workfront

[AJRa]

I'm having a similar problem, but used this thread to help me get part of the way. I'm trying to build a report that calculates the number of days an item in our queue is left unresolved. At the moment, I have successfully created a column for "Request Duration" that calculates this: displayname=Request Duration querysort=requestDuration textmode=true valueexpression=DATEDIFF({actualCompletionDate},{entryDate}) valueformat=int The reason we want to report on this is so we can demonstrate our team's ability to resolve open items quickly - the lower the number, the better our team is doing at addressing open requests. I'd also like to show how this number has improved over time. Knowing that, here are the things I'm trying to figure out. 1. How can I sort my Detail Tab by this custom column? Every time I try, I get an error that forces me to refresh the page. 2. How can I create a chart that helps me compare this custom column against other values within Workfront? At the moment, the custom column does not show up as an option for any of the fields in the chart view. 3. Am I overcomplicating this, and is there a better way of demonstrating the information I'm trying to communicate? Any help is appreciated. Thanks! AJ Rahm Corporate Marketing

[Doug_Den_Hoed__AtAppStore]

Hi AJ, In order to sort or chart, Workfront requires that the data item of interest (Request Duration, in your case) be physically stored in its database. So, you're close. If you add a custom for to each Task and move your calculation into a Custom Parameter, you can then change your column definition slightly, to this (in textmode; noting that -- even easier -- you could select it in standard mode): displayname=Request Duration querysort= {DE:Request Duration} textmode=true value field = {DE:Request Duration} valueformat=int From there, you can then also repeat adding the column with Min / Max / Average on each (if you wish), and -- even groovier -- use Request Duration on reports charts. Regards, Doug Doug Den Hoed - AtAppStore