Getting timestamp from status changes | Community
Skip to main content
DreadkiailiWork
Level 4
Apr 22, 2020 9:54 PM UTC
Question

Getting timestamp from status changes

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.

    15 replies

    Doug_Den_Hoed__AtAppStore
    Doug_Den_Hoed__AtAppStore
    New Participant
    Oct 7, 2020 9:27 PM UTC

    Hi Jeremy,

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

    Regards,

    Doug

    Translate
    kynabaker16
    kynabaker16
    Employee
    Apr 29, 2020 9:03 PM UTC

    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.

    Translate
    JeremyBu1
    Level 2
    Oct 7, 2020 7:27 PM UTC

    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

    Translate
    kynabaker16
    kynabaker16
    Employee
    Oct 7, 2020 7:29 PM UTC

    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!

    Translate
    HectorMe2
    Level 2
    Apr 28, 2020 10:59 PM UTC

    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.

    Translate
    Heather_Kulbacki
    Heather_Kulbacki
    Level 2
    Apr 24, 2020 8:25 PM UTC

    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.

    Translate
      EricMa
      Level 4
      Apr 24, 2020 9:18 PM UTC

      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

      Translate
        Heather_Kulbacki
        Heather_Kulbacki
        Level 2
        Apr 23, 2020 4:44 PM UTC

        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.

        Translate
          EricMa
          Level 4
          Apr 24, 2020 6:53 PM UTC

          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

          Translate
          Heather_Kulbacki
          Heather_Kulbacki
          Level 2
          Apr 23, 2020 12:15 PM UTC

          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.

          Translate
          DreadkiailiWorkAuthor
          Level 4
          Apr 23, 2020 4:27 PM UTC

          Yes, that would be good for going forward.

          Translate
          Terms & ConditionsAccessibility statement

          Loading footer...