2 Replies Latest reply: Jun 22, 2016 12:37 PM by Stefan Wühl RSS

    AND OR Nested IF statements

    Ruan Greeff

      Hi,

       

      i want to create a flag if a date falls into two dates (day or (day +1))  AND the hours in the day falls between 21:00 and 09:00

       

      because of the date change at midnight i need to ensure that both dates are checked.

       

      this is in order to divine Shift times: dayshift is between 09:00-21:00 (will be the same day) and nightshift will be 21:00-09:00 meaning that the day increases by one.

       

      Example:

       

      Shift will run from 22.06.2016 21:00 to 23.06.2016 09:00.  I am currently using these dates in TIMESTAMP format.

       

      To get an idea of what i want to accomplish please have a look at the code below :

       

      Day(Timestamp([TSA_ROUTE_DEP_1],'YYYY/MM/DD hh:mm:ss'))    /////this will be in order to get the current date



      OR


      Day(Timestamp(Interval(Timestamp([TSA_ROUTE_DEP_1],'YYYY/MM/DD hh:mm:ss') + '24:00:00','hh:mm:ss'),'YYYY/MM/DD hh:mm:ss'))                                                //// this will increase the day + 1

       

       

      AND

       

      Hour(TIMESTAMP([TSA_ROUTE_DEP_1],'hh:mm:ss')) ////////////  >=21:00 <=09:00



      In summary, i want to check IF a date is equal to the date selected or the day after and flag all the data within the hours of 21:00 and 09:00.



      For example  if Nightshift is selected for the 22nd the following should flag.

       

      22.04.2016 21:00        X       

      23.04.2016 08:50        X

      22.04.2016 23:00        X

      24.04.2016 10:00

       

      Hope this helps,Any advice will be appreciated.

       

      Thanks in advance !