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

    Reference Time in If Statements



      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.




      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?



          • Re: Reference Time in If Statements
            Marco Wedel



            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


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


            should be the same as


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

            hope this helps