1 Reply Latest reply on Feb 13, 2019 3:54 PM by Sujay Paranjpe

    Comparing DateTimes in Calculated Fields

    Miller Love

      I have a data set where each record has a scheduled delivery time and a latest allowable delivery time. I am trying to create a calculated field which reads "Yes" when the delivery time is before the latest allowable time and "No" when it does not. The code below should return "Yes" for 4 records in my data, but is currently returning "No" for all records. What am I missing here? (Note: the datetimes were just times in the original data source, so they all share the same date of 12/30/1899)

       

      IF [Delivery Day]="B" AND DATETIME([Delivery Time])>=DATETIME([Latest Arrival Time (24hr Format)])

       

       

      THEN "Yes"

       

       

      ELSE "No"

       

       

      END

        • 1. Re: Comparing DateTimes in Calculated Fields
          Sujay Paranjpe

          HI Miller Love

           

          I am trying to create a calculated field which reads "Yes" when the delivery time is before the latest allowable time and "No" when it does not.
          IF [Delivery Day]="B" AND DATETIME([Delivery Time])>=DATETIME([Latest Arrival Time (24hr Format)])

           

          Shouldn't that condition be

           

          IF [Delivery Day]="B" AND DATETIME([Delivery Time]) < DATETIME([Latest Arrival Time (24hr Format)])

           

          i.e use less than operator?

           

          Best,

          Sujay

          If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.