4 Replies Latest reply on Apr 7, 2017 11:27 PM by Ajeet ...

    date difference irrespective of start and end date

    Ajeet ...

      Hi guys,

       

      I have to create a calculation with the logic:

           Day ( date-time-year field1) < Day ( date-time-year field2) :::::::  output would look like ::::: 31 March 2017 < 2 April 2017

      Though i can use DATEDIFF function but i have to specify end and start date which i do not want. Similarly, DATETPART function gives only integral values, and DATETRUNC function gives day with time as well which is not helpful in my case as i want to compare with two days only. ANy help please

       

       

      Thanks !

      Ajeet

        • 1. Re: date difference irrespective of start and end date
          Norbert Maijoor

          Hi Ajeet,

           

          Not sure but find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.

           

           

          1. Notation: if [field1]<[field2] then str(int([field 1 Day]))+" "+[Month Field 1]+" "+[field 1 Year]+" < "+str(int([field 2 Day]))+" "+[Month Field 2]+" "+[field 2 Year] else "" END

           

          2. Field 1 Day: mid(str([field1]),9,2)

          3. Field 2 Day: mid(str([field2]),9,2)

           

          4. Field 1 Month: mid(str([field1]),6,2)

          5. Field 2 Month: mid(str([field2]),6,2)

           

          7. Field 1 Year: mid(str([field1]),1,4)

          8. Field 2 Year: mid(str([field2]),1,4)

           

          9. Month Field 1:

          if int([field 1 Month])=1 then "January"

          ELSEIF int([field 1 Month])=2 then "February"

          ELSEIF int([field 1 Month])=3 then "March"

          ELSEIF int([field 1 Month])=4 then "April"

          ELSEIF int([field 1 Month])=5 then "May"

          ELSEIF int([field 1 Month])=6 then "June"

          ELSEIF int([field 1 Month])=7 then "July"

          ELSEIF int([field 1 Month])=8 then "Augustus"

          ELSEIF int([field 1 Month])=9 then "Septemer"

          ELSEIF int([field 1 Month])=10 then "October"

          ELSEIF int([field 1 Month])=11 then "November"

          ELSEIF int([field 1 Month])=12 then "December"

          END

           

          10. Month Field 2:

          if int([field 2 Month ])=1 then "January"

          ELSEIF int([field 2 Month ])=2 then "February"

          ELSEIF int([field 2 Month ])=3 then "March"

          ELSEIF int([field 2 Month ])=4 then "April"

          ELSEIF int([field 2 Month ])=5 then "May"

          ELSEIF int([field 2 Month ])=6 then "June"

          ELSEIF int([field 2 Month ])=7 then "July"

          ELSEIF int([field 2 Month ])=8 then "Augustus"

          ELSEIF int([field 2 Month ])=9 then "Septemer"

          ELSEIF int([field 2 Month ])=10 then "October"

          ELSEIF int([field 2 Month ])=11 then "November"

          ELSEIF int([field 2 Month ])=12 then "December"

          END

           

          Regards,

          Norbert

          • 2. Re: date difference irrespective of start and end date
            Ben Neville

            Hi Ajeet - can you elaborate a bit more? It's difficult to say exactly what you're trying to accomplish. Perhaps a spreadsheet/screenshot/workbook would help?

            • 3. Re: date difference irrespective of start and end date
              seeta t

              Hi Ajeet,

               

              Not sure this is what you are excepting.

              i am adding sample calculation and result as well.

               

              if [Start Date]>[end date]

              THEN 

              str(DAY([Start Date]))+" "+DATENAME('month',[Start Date])+" "+STR(YEAR([Start Date]))+">"+

              str(DAY([end date]))+" "+DATENAME('month',[end date])+" "+STR(YEAR([end date]))

              END

               

               

              seeta

              • 4. Re: date difference irrespective of start and end date
                Ajeet ...

                Norbert Maijoor

                Ben Neville

                seeta t

                Sorry for the inconvenience caused: Here is the exact problem:

                I have two fields:

                - date-time1 field with inputs as 1 April 2017 01:00:00 PM

                -date-time2 field with inputs as 2 April 2017 09:00:00 PM

                 

                The intent is to achieve the following output:

                     if    Day(date-time1)    >    Day(date-time2)  then 1 else 0 end

                 

                Thus, my calculation only takes input Day-mmm-yyyy

                 

                Thanks !