Calculated Column Help - looking for a date difference between a custom field and actual completion date | Community
Skip to main content
K
KristinaWa1
October 5, 2023
Solved

Calculated Column Help - looking for a date difference between a custom field and actual completion date

  • October 5, 2023
  • 1 reply
  • 1586 views

Hello,

I have a pacing reporting I am trying to create. It is a task report that is looking for our certain tasks we consider milestones for revenue recognition. 

I have the report set up pulling in all the columns I want, but I am having trouble getting the correct formula for text mode in a column.

I have two field I want to find the duration of. I have the Task Actual Completion Date and then a custom field housed on the project level called Sign Date. So I am trying figure out how much time it took from signature, "Sign Date", till the task was completed. I have tried using the below but no results are showing.

Thank you and I appreciate any help!

 

Kristina

 

valueexpression=CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

valuefield=DE:Sign Date

linkedname=direct

valueformat=HTML

aggregator.valueexpression= CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

aggregator.function=SUM

aggregator.valueformat=HTML

aggregator.displayformat=HTML

displayname=Milestone Pacing

namekey=$$TODAY

textmode=true

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by ajdifonzo

    Thank you @doug_den_hoed__atappstore and @ajdifonzo ,

     

    I used that formula with the added missing bracket, and I am not getting a full answer back. They are all saying just "Days" with no actual number added even if both fields have dates listed. Is there something I need to add in as the Sign Date is on the project level and the actual completion is on the task level? I tried added in "project." to the Sign Date in the formula that them removed the Days from the column.

     

    Kristina


    I should have asked this to start with, what type of report are you creating?  If this is a Task report, then yes you will need to add {project}. 

     

    valueexpression=CONCAT(ROUND(DATEDIFF({project}.{DE:Sign Date},{actualCompletionDate}),2)," Days")

    1 reply

    Doug_Den_Hoed__AtAppStore
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity Advisor
    Community Advisor
    October 5, 2023


    Hi @kristinawa1,

     

    Texmode expects either valuefield or valueexpression (but not both), so I suggest you try removing the former so the latter kicks in.

     

    valueexpression=CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

    valueformat=HTML

    aggregator.valueexpression= CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

    aggregator.function=SUM

    aggregator.valueformat=HTML

    aggregator.displayformat=HTML

    displayname=Milestone Pacing

    textmode=true

     

    Regards,

    Doug

      ajdifonzo
      ajdifonzo
      October 5, 2023

      As usual, I agree with Doug, but you are also missing a bracket, I added and highlighted in red below.  

       

      valueexpression=CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate}),2)," Days")

        K
        KristinaWa1Author
        October 5, 2023

        Thank you @doug_den_hoed__atappstore and @ajdifonzo ,

         

        I used that formula with the added missing bracket, and I am not getting a full answer back. They are all saying just "Days" with no actual number added even if both fields have dates listed. Is there something I need to add in as the Sign Date is on the project level and the actual completion is on the task level? I tried added in "project." to the Sign Date in the formula that them removed the Days from the column.

         

        Kristina

        Terms & ConditionsAccessibility statement

        Loading footer...