Between dual time range

[briareu]

Hey guys,
I need help with a formula : in A2 I have a customer arrival time 9:48. The time window the customer can be delivered is between 8:00 (B2)- 13:00(C2) and 15:00 (D2) - 18:00 (E2).
How can I see if the order was delivered outside the time windows (before 8 after 13, between 13:00 and 15:00 and after 18:00.
Note: For some customers I have either the first time windows either the second (the formula would be impacted?)

Thank you in advance

 

[xyga1998]

Try this

<b2,"outside the="" time="" windows",if(and(a2="">=IF(A2<B2,"Outside the time windows",IF(AND(A2>C2,A2<D2),"Outside the time windows",IF(A2>E2,"Outside the time windows","")))

Keep in mind that if the customer is delivered at 18:00:10 it will show as outside of time window but the cell value will be 18:00. If you dont have seconds included in your values then it is ok. If the seconds are included but you still want to pardon 59 seconds then you should add 59 seconds at B2, C2, D2 and E2 xx:00:59. If you want to be exact then tell me and I will do it when I have time</b2,"outside>

 

[xyga1998]

*Sorry for the above
Try this

<b2,"outside the="" time="" windows",if(and(a2="">

=IF(A2<B2,"Outside the time windows",IF(AND(A2>C2,A2<D2),"Outside the time windows",IF(A2>E2,"Outside the time windows","")))

Keep in mind that if the customer is delivered at 18:00:10 it will show as outside of time window but the cell value will be 18:00. If you dont have seconds included in your values then it is ok. If the seconds are included but you still want to pardon 59 seconds then you should add 59 seconds at B2, C2, D2 and E2 xx:00:59. If you want to be exact then tell me and I will do it when I have time</b2,"outside>

 

[xyga1998]

I don't know what is happening. Gonna try one last time to post the solution

IF(A2<B2,"Outside the time windows",IF(AND(A2>C2,A2<D2),"Outside the time windows",IF(A2>E2,"Outside the time windows","")))

Keep in mind that if the customer is delivered at 18:00:10 it will show as outside of time window but the cell value will be 18:00. If you dont have seconds included in your values then it is ok. If the seconds are included but you still want to pardon 59 seconds then you should add 59 seconds at B2, C2, D2 and E2 xx:00:59. If you want to be exact then tell me and I will do it when I have time</d2),"outside></b2,"outside>

 

[briareu]

I don't know what is happening. Gonna try one last time to post the solution

IF(A2C2,A2E2,"Outside the time windows","")))

Keep in mind that if the customer is delivered at 18:00:10 it will show as outside of time window but the cell value will be 18:00. If you dont have seconds included in your values then it is ok. If the seconds are included but you still want to pardon 59 seconds then you should add 59 seconds at B2, C2, D2 and E2 xx:00:59. If you want to be exact then tell me and I will do it when I have time</d2),"outside></b2,"outside>

Thank you, the solution works, however I have another question related to the topic - when D2 and E2 is empty - no time window - excel will see it as 0 and it will skew the output, is there anything I can do to avoid that (I tried deleting the content in the cell and did not worked)

 

[xyga1998]

Thank you, the solution works, however I have another question related to the topic - when D2 and E2 is empty - no time window - excel will see it as 0 and it will skew the output, is there anything I can do to avoid that (I tried deleting the content in the cell and did not worked)

If the time windows for you are only 08:00-13:00 and 15:00-18:00 check the code below

 =IF(A2<TIME(8,0,0),"Outside the time windows",IF(AND(A2>TIME(13,0,0),A2<TIME(15,0,0)),"Outside the time windows",IF(A2>TIME(18,0,0),"Outside the time windows","On time")))

If this doesn't suit you then tell me as the formula needs to be changed

 

[briareu]

If the time windows for you are only 08:00-13:00 and 15:00-18:00 check the code below

 =IF(A2TIME(13,0,0),A2TIME(18,0,0),"Outside the time windows","On time")))

If this doesn't suit you then tell me as the formula needs to be changed

Everything works now,
Thank you for your help</time(15,0,0)),"outside></time(8,0,0),"outside>