Getting Delivery Status based on Specific Time Window

[ranz_vincent]

Having problems again with data that uses time. I wanted to see if our deleveries are arriving within a specific time window per customer but the problem is there are no Late results showing from my conditional column. "time_arrived_to_store" should be within "Sheet1.DRY - Arrival Time From" and "Sheet1.DRY - Arrival Time To". One data that I saw was delivery was made at 6am and the window was 4pm to 10pm it should be late but it was tagged on time

[shafiz_p · Accepted solution]

Hi @ranz_vincent  You said, "time_arrived_to_store" should be within "Sheet1.DRY - Arrival Time From" and "Sheet1.DRY - Arrival Time To" meaning boundary, so you need to use AND condition but you have used OR. So any condition which is true will return result and discarded other condition.

Your mentioned specific case, 6 am comes before 4 or 10 pm, and in both the condition your return result is on time. So, arrived time to store is less than 10 PM , condition 2 is true and returned "On Time", and it discarded other condition.

This formula works as follows:

• If Arrived to Store is between Arrived from and Arrived to (inclusive), it returns "On Time".
• If Arrived to Store is less than Arrived from, it returns "Early".
• If Arrived to Store is greater than Arrived to, it returns "Late".

You could try to add a Custom Column with the following formula:

= if [Arrived to Store] >= [Arrived from] and [Arrived to Store] <= [Arrived to] then "On Time"
  else if [Arrived to Store] < [Arrived from] then "Early"
  else "Late"

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[danextian]

Hi @ranz_vincent 
As always, please post a workable sample data and your expected result from that.