4 Replies Latest reply: May 5, 2015 7:45 PM by Marco Wedel RSS

    Reference Time in If Statements

      Hi,

       

      I am trying to find out the number of days it takes to ship orders - but any order placed after 4 pm cannot be shipped the same day and therefore should not have to count the actual CompletedDate as a day.

       

      In essence, if an order that is placed at noon, and ships the same day should have 0 days to ship, but an order placed that same day at 8 pm, then ships the following day should also show 0 days.

       

      I am trying to use an If Statement, so that if the order is placed past 4 pm, then it takes one day off of the shipping days.

       

      Example:

       

      CompletedTimeCompletedDateShipDate
      8:00:00 AM1/1/20151/1/2015
      5:00:00 PM1/1/20151/2/2015
      12:00:00 PM1/1/20151/5/2015

       

       

      The first two entries should both have 0 days to ship - while the last one should have 5.

       

       

      My problem is referencing time in my If Statement - it doesn't want to recognize the various formats I am trying to use.

       

      IF(CompletedTime>='4:00:00 PM',(Date(ShipDate)-Date(CompletedDate) -1), Date(ShipDate)-Date(CompletedDate))

       

      It keeps telling me all my orders are after 4 pm, when they aren't. (I tested this by doing an If statement like above, only where the then condition just told me "After 4" instead of doing the equation.)

        • Re: Reference Time in If Statements
          Steve Dark

          Hi Andrea,

           

          You need to keep in mind that with a data, if it's stored correctly, that a day is actually a value of 1, and a time is a decimal portion of that, e.g. midday is .5.

           

          Given this, you can work out the time to compare to with a sum:

           

          if(CompletedTime >= (1/24*16),

           

          This assumes that the CompletedTime field only has the time in, and not date time.  If it is date time the code becomes:

           

          if((CompletedTime - DayStart(CompletedTime)) >= (1/24*16),


          Here you are effectively removing all the days from the time value (i.e. up to midnight at the start of the day) leaving only the decimal portion.

           

          I hope that all makes sense?

           

          Steve

          • Re: Reference Time in If Statements
            Marco Wedel

            Hi,

             

            I was just about to post a (wrong) solution suggesting that you might be comparing strings rather than numbers due to a non-matching TimeFormat variable, when I found your thread already answered correctly.

             

            Just some thoughts to supplement the solution:

             


            CompletedTime - DayStart(CompletedTime)

             

            might as well be calculated as


            Frac(CompletedTime)


            which extracts the (fractional) time part from a timestamp value.





            Date(ShipDate)-Date(CompletedDate)


            should be the same as


            ShipDate-CompletedDate


            since the formatting Date()-function does not change the numerical value of a date/timestamp,



            hope this helps


            regards


            Marco