9 Replies Latest reply: Jul 27, 2017 2:23 PM by Eddy Sanchez RSS

    Difference between two dates in Year,Month and Days

    Muhammad Irfan

      I have tried calculating difference between two dates example Qvw is attached it works for every date but only if the the end date's month is greater than the start. please see the image which is the problem.

        • Re: Difference between two dates in Year,Month and Days

          Dear Irfan.

           

          i think you need to use fab function when ever your geting - value

           

          and in the Year field you need to use floor function to get the exact Year..

           

          kindly find the attached qvw.

           

          i hope it will help you.

           

          thanks,

          Mukram.

          • Re: Difference between two dates in Year,Month and Days
            Gysbert Wassenaar

            Try these:

             

            Months:

            if(day([End Date])>=day([Start Date]),

            num(month(MonthStart([End Date])-MonthStart([Start Date])))-1,

            num(month(MonthStart([End Date])-MonthStart([Start Date])))-2)

             

            Days:

            if(day([End Date])>=day([Start Date]),

            day([End Date])-day([Start Date]),

            [End Date]-MakeDate(year([End Date]),month([End Date])-1,day([Start Date])))

            • Re: Difference between two dates in Year,Month and Days

              How about this?

               

              IF

              ([End Date] <= AddYears([Start Date],1),'0 Years ',
              Age([End Date],[Start Date]) & ' Years ')
              &
              IF(MONTH([End Date]) = MONTH([Start Date]),'0 Months ',
              IF(DAY([Start Date]) < DAY([End Date]), fabs(MONTH([End Date])-Month([Start Date])) & ' Months ',
              ((
              MONTH([End Date])+12)-Month([Start Date]))-1  & ' Months '
              ))
              &
              IF(MONTH([End Date]) = MONTH([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
              IF(DAY([End Date]) > DAY([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
              DAY(DATE(Interval([End Date] - [Start Date])))-1 & ' Days'
              ))

               

                • Re: Re: Difference between two dates in Year,Month and Days

                  Thanks everyone for this. I needed to do exactly that. I made myself an example to make all this work and I would like to share it with you.

                   

                  Complete fully working qvw and source code. Thanks to Haider Al-Seaidy

                  for his correct answer. It's exactly what I needed.

                   

                  Eric Tremblay in Montreal, Canada

                   

                   

                  // Sample data

                  Data_test:
                  load
                  date(date1,'YYYY-MM-DD') As ORDER_DATE;
                  LOAD * INLINE [
                  date1
                  2011-01-15,
                  2011-02-18,
                  2008-01-15,
                  2010-02-18,
                  2007-01-15,
                  2009-02-18,
                  2012-01-15,
                  2014-02-18,
                  ]
                  ;

                  Temp:
                  LOAD
                  Min(date(ORDER_DATE,'YYYY-MM-DD')) as MinDate,
                  Max(date(ORDER_DATE,'YYYY-MM-DD')) as MaxDate
                  Resident Data_test;

                  LET vMinDate = floor(peek('MinDate'));
                  LET vMaxDate = floor(peek('MaxDate'));

                  // This section here will calculate
                  // the age between the smallest and the largest dates
                  min_max_order_dates:
                  load

                  // Original Code by Haider Al-Seaidy. Thanks!
                  IF (MaxDate <= AddYears(MinDate,1),'0 Years ',
                  Age(MaxDate,MinDate) & ' Years ')
                  &
                  IF(MONTH(MaxDate) = MONTH(MinDate),'0 Months ',
                  IF(DAY(MinDate) < DAY(MaxDate), fabs(MONTH(MaxDate)-Month(MinDate)) & ' Months ',
                  ((
                  MONTH(MaxDate)+12)-Month(MinDate))-1 & ' Months '
                  ))
                  &
                  IF(MONTH(MaxDate) = MONTH(MinDate),DAY(MaxDate) - DAY(MinDate) & ' Days',
                  IF(DAY(MaxDate) > DAY(MinDate),DAY(MaxDate) - DAY(MinDate) & ' Days',
                  DAY(DATE(Interval(MaxDate - MinDate)))-1 & ' Days'
                  ))
                  As AGE_BETWEEN_MinDate_MaxDate

                  Resident Temp; // From this table

                  Drop Table Temp;

                  • Re: Difference between two dates in Year,Month and Days
                    Eddy Sanchez

                    The "correct answer" of this thread is not correct.

                     

                    I do the next:

                     

                    =If(day([End Date])>=day([Start Date]),

                    div(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))),12) & ' Years'

                    &' '&

                    mod(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))),12) & ' Months'

                    &' '&

                    ([End Date]-AddMonths([Start Date],((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))))) & ' Days'

                    ,

                    div(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1,12) & ' Years'

                    &' '&

                    mod(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1,12) & ' Months'

                    &' '&

                    ([End Date]-AddMonths([Start Date],((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1)) & ' Days'

                    )

                  • Re: Difference between two dates in Year,Month and Days
                    Nico Manro Oberholzer

                    I think this is probably the simplest way...

                    =((Date1- Date2) / 365) * 12

                    "=num(((vStartDate - vEndDate) / 365) * 12,'##')"

                     

                    - Given that you initially convert your Dates (For purposes of the Sum) into Number / integer format.

                     

                    Lemme Know if it worked for you too?

                     

                    Rgds,

                    Nico Oberholzer