Getting timestamp from status changes | Community
Skip to main content
D
DreadkiailiWork
Level 4
April 22, 2020
Question

Getting timestamp from status changes

  • April 22, 2020
  • 6 replies
  • 2897 views

Does anyone have a way to get the date stamp when an issue/request changed to a specific status? This is for things that have happened in the past. We use Awaiting Feedback when a request doesn't have enough info, then the requestor changes to Resubmitted when they fix it. We want to know how many of our requests had to be resubmitted.

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

    6 replies

    Heather_Kulbacki
    Community Advisor
    Heather_KulbackiCommunity Advisor
    Community Advisor
    April 23, 2020

    I have a couple calculated custom fields that I use for this, but they won't calculate what has happened in the past. Once they are set up, they will keep a running account of status changes and date, so once they're set up they're great as you move forward, but won't help with what has already happened.

    I can get you the calculations if you're interested.

    D
    DreadkiailiWorkAuthor
    Level 4
    April 23, 2020

    Yes, that would be good for going forward.

    Heather_Kulbacki
    Community Advisor
    Heather_KulbackiCommunity Advisor
    Community Advisor
    April 23, 2020

    It's a two-step process with one calculated field to capture the current status, then a second field to capture that status, the date and any prior statuses/dates that were already in that field.

    my first field is labeled: Current Status and has this calculation:

    IF(Status="CUR","Production",IF(Status="GPO","HPF", IF(Status="PLN","Planning", IF(Status="EGZ","Scheduling", IF(Status="CPL","Completed", IF(Status="MRK","ProdBacklog",Status))))))

    you'd need to substitute in the statuses you want to capture and their keycodes.

    my second field is labeled: Status History and has this calculation:

    LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW,") | ",Status History)),2000)

    the "2000" at the end of this calculation limits the field to 2000 characters.

    They come out looking something like this:

    Current Status

    Production

    Status History

    Production (6/19/19) | HPF (6/19/19) | Planning (6/19/19) | Scheduling (6/19/19) | Production (5/31/19) | HPF (5/16/19) | Production (5/15/19) | Planning (5/13/19) | Production (4/25/19) | Completed (4/25/19) | Production (4/9/19) | Scheduling (4/9/19) | Production (4/8/19) | HPF (4/8/19) |

    This project is one I do testing with so the status has jumped around a bit.

      E
      EricMa
      Level 4
      April 24, 2020

      Hi, Heather. I was intrigued by your "two-step" calculated field approach and wanted to apply it to our Project custom forms. I was able to get the Current Status field entered, but I'm having trouble with the Status History calculation (which I THOUGHT would be easier, since I'm just copying your code). I've entered

      LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status,Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW,") | ",Status History)),2000)

      ...but I'm getting "Custom Expression Invalid" when I do. Am I missing something?

      Thanks!

      ~Eric

      Heather_Kulbacki
      Community Advisor
      Heather_KulbackiCommunity Advisor
      Community Advisor
      April 24, 2020

      I remember that bugger of a field... since the field references itself in the calculation, you'll need to create the field and save it before you can reference it in the calculation.

      So create the field with no calculation, save your form. Then go back into the form and paste in the calculation.

        E
        EricMa
        Level 4
        April 24, 2020

        Ah, yes, I remember having to do that with a calculated field once a long time ago. Tricky little buggers, indeed.

        I followed your advice, and it's working beautifully now. Thanks for your help!

        ~Eric

          H
          HectorMe2
          Level 2
          April 28, 2020

          Ohh I like this solve, is there any way you can capture the users who completed the task?

          We have tasks that touched multiple times in one master project. If we could capture the user of the current status would be awesome.

          kynabaker16
          Adobe Employee
          kynabaker16Adobe Employee
          Adobe Employee
          April 29, 2020

          Hey all - we also have a helpful blog about using calculated fields to keep track of dates and details that might be useful in this use case as well.

          J
          JeremyBu1
          Level 2
          October 7, 2020

          Hi Kyna,

          The blog post doesn't appear to be working correctly. Could you repost?

          Im really interested in knowing how to capture the date of when an issue status changes from new to In-progress.

          Thanks,

          Jeremy

          kynabaker16
          Adobe Employee
          kynabaker16Adobe Employee
          Adobe Employee
          October 7, 2020

          Yes! That's odd. Here's the link: https://one.workfront.com/s/managed-content-blogs/save-time-using-calculated-fields-to-capture-dates-details-and-data-20Y0z000000bmROEAY and I updated the original one as well!

          Doug_Den_Hoed_AtAppStore
          Community Advisor
          Doug_Den_Hoed_AtAppStoreCommunity Advisor
          Community Advisor
          October 7, 2020

          Hi Jeremy,

          For more on the subject, I invite you to read this Targeted Auditing Proof of Concept post.

          Regards,

          Doug

          Terms & ConditionsAccessibility statement

          Loading footer...