2 Replies Latest reply: Aug 24, 2016 1:39 PM by Prashanth Reddy RSS

    Duration between two dates?

    Prashanth Reddy

      Hi

      I have data set as shown below.

       

      travelrule.PNG

      Here, employer is travelling from Chennai to Delhi via Banglore.

      -> Identify Earliest start date and latest start  time (min date , max time)

      -> Identify Latest start date and earliest start time (max date, min time)

      -> difference between these dates and times is duration of stay in main location(Delhi).

      -> shown the scenario in Yellow in above table for clear understanding

       

      How can achieve this?

       

      Regards

      Prashanth

        • Re: Duration between two dates?
          André Gomes

          Hi,

           

          If you want to calculate without exclude any holiday, then you can use =max Date - min Date

          And if you want to calculate working day, then use =Networkdays (max Date, Min Date {, Holiday})

           

          The Networkdays () returns the number of working days (Monday-Friday) between and including StartDate and EndDate taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

          Examples:

          networkdays ('2007-02-19', '2007-03-01') returns 9

          networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

           

          HTH

           

          André Gomes

            • Re: Duration between two dates?
              Prashanth Reddy

              Hi Andre,

               

              Thanks for the reply. but that is not what I am looking for. Of the same Start date column I need to derive the

              (Max date with earliest time(Start Date) - Min date with latest time(Start Date)).

              Example from data set.

              (7/29/2016 14:29 - 07/25/2016 10:50) grouped by PNR.

               

              Regards

              Prashanth