2 Replies Latest reply on Jul 9, 2018 12:41 AM by kris.zaliauskas

    How to compare timestamps to an hour-time (e.g. 10:30), not for a specific day?

    kris.zaliauskas

      Hello,

       

      Attached in the workbook (v.10.5) I have incoming payments data which have 2 variables "Timestamp in" and "Timestamp out".

       

      All the payments are manually checked when they come into the system (timestamp in) and if they are not released by 10:30 then the payment will only be processed the next day, which is bad.

       

      Alternatively if the payment comes in after 10:30 (for e.g. 10:35) then the deadline would be next day at 10:30. Since the client made the payment after the deadline, therefore, we would look at tomorrow's day  10:30 as the deadline.

       

      So I would simply like to calculate, which payments missed the deadline:

      - in case #1 (when timestamp_in is < 10:30), but timestamp_out is greater than 10:30

      AND

      - case #2 (when timestamp_in is > 10:30) and timestamp_out is greater than tomorrow at 10:30

       

      This seemed like a pretty simple calculation at first but I ended having to write an insane amount of code.

       

      4 sets of ORs for when the timestamp_in day = timestamp_out day

      and another 4 sets when  timestamp_in day <> timestamp_out day

       

      You would think there would by a way to compare a timestamp with a specific hour and time (10:30) for a non-specific day, but that doesn't seem to be possible.

       

      Any help to simplify this would be greatly appreciated as there are many different deadlines times depending on other variables not available in the test data provided. So copy pasting this huge code block 10+ times is very frustrating.

       

      if
      
      (//if close date =in date, next 4 ifs
      
      (datepart('hour',([Timestamp In])) = 10 and datepart('minute',([Timestamp In]
      )) < 30 and datepart('hour',([Timestamp Out]))=10 and datepart('minute',([Timestamp In]
      )) > 30  and date([Timestamp In])=date([Timestamp Out]) )
      
      or
      
      //if in hour is 10 and close hour is not 10
      (datepart('hour',([Timestamp In])) = 10 and datepart('minute',([Timestamp In]
      )) < 30 and datepart('hour',([Timestamp Out]))> 10
      and date([Timestamp In])=date([Timestamp Out]) )
      
      or
      
      //if in hour is not 10 and close hour is  10
      (datepart('hour',([Timestamp In])) < 10  and datepart('hour',([Timestamp Out]))=10 and datepart('minute',([Timestamp In]
      )) > 30  and date([Timestamp In])=date([Timestamp Out]) )
      
      
      or
      
      
      //if in hour is not 10 and close hour is not  10
      (datepart('hour',([Timestamp In])) < 10 and datepart('hour',([Timestamp Out]))> 10
      and date([Timestamp In])=date([Timestamp Out]) )
      
      
      or
      //if close date > in date, next 4 ifs
      
      
      //if in  hour is 10 and close hour is 10
      (datepart('hour',([Timestamp In])) = 10 and datepart('minute',([Timestamp In]
      )) < 30 and datepart('hour',([Timestamp Out]))=10 and datepart('minute',([Timestamp In]
      )) > 30  and date([Timestamp In])<date([Timestamp Out]) )
      
      
      or
      
      
      //if in hour is 10 and close hour is not 10
      (datepart('hour',([Timestamp In])) = 10 and datepart('minute',([Timestamp In]
      )) < 30 and datepart('hour',([Timestamp Out]))> 10
      and date([Timestamp In])<date([Timestamp Out]) )
      
      
      or
      //if in  hour is not 10 and close hour is  10
      (datepart('hour',([Timestamp In])) < 10  and datepart('hour',([Timestamp Out]))=10 and datepart('minute',([Timestamp In]
      )) > 30  and date([Timestamp In])<date([Timestamp Out]))
      
      
      or
      
      
      //if in hour is not 10 and close hour is not  10
      (datepart('hour',([Timestamp In])) < 10 and datepart('hour',([Timestamp Out]))> 10
      and date([Timestamp In])<date([Timestamp Out])))
      
      
      
      
      then 'Missed'
      else 'Not missed'
      
      
      
      
      end