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

    AND OR Nested IF statements

    Ruan Greeff



      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.




      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


      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





      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 !