1 Reply Latest reply on Jan 2, 2020 6:29 PM by swaroop.gantela

    Getting the number of days & removing time stamped.

    Jean Davis

      Hello,

       

       

      I'm trying to get the number of days worked on a project by using two date fields "Start/Modified." To get the actual date, I would like to remove the time-stamped from "Modified/Activity"; then compare the last date within the Activity field with the Modified field, and if it's the same, use the Modified date else use Activity.

       

       

      I had to remove the rest of the data because of privacy policies.

       

       

      Thank you

       

       

      Jean

        • 1. Re: Getting the number of days & removing time stamped.
          swaroop.gantela

          Jean,

           

          I think it is doable, but wanted to get a few clarifications.

          I wasn't clear what each row of your dataset is.

          Is each row a Reference Number with startdate, modifydate, activitydate?

          And one reference number could have multiple activity dates?

          Is the goal then to get the number of days for each reference number?

          When you are looking for the last Activity date, is that the last date for a reference number?

           

          Would be grateful if you would fill in your expected mod/activity dates in the attached table.

           

          In general, the form calculated field would look something like:

           

          IF DATETRUNC( 'day', [Modify Date] ) = DATETRUNC('day', {FIXED [Number]:MAX([Activity Date])})

          THEN DATETRUNC( 'day', [Modify Date] )

          ELSE DATETRUNC( 'day', [Activity Date] )

          END

           

          But I'm not sure about the last part.