How to check if a time noted is within designated window or range in Excel

[nk_aspire]

I need to check if a time of collection is within allowed time window or not.

For e.g.

A B C
1 2/1/2017 10.36 2/1/2017 10.30 1 min out of +/- 5 minutes window
2 2/1/2017 10.24 2/1/2017 10.30 1 min out of +/- 5 minutes window

A1 here time of collection of data (date included), and B1 is the scheduled time. The acceptable window or range for this is +/- 5 minutes. In this case the time of collection is 1 min out of +/- 5 minutes window. Similar example is shown in row 2. How can i get C1 to show the message or something similar as above which can account for + 5 minutes (after scheduled time- late) and - 5 minutes (before scheduled time-early) scenarios?.

Thank you!.

[Pete_UK]

Are those proper date and time values, or text values which look like date/times? The reason for asking is that times are normally shown separated by a colon ( : ), like 10:36, so the formula may be affected by this.


Pete

[JLGWhiz]

If the date and time in columns A and B are in fact date data types, you might be able to use something like this

Please Login or Register  to view this content.

The 0.0034722 should equate to 5 minutes usinge the date value.

[nk_aspire]

Can i get a solution in the form of a formula ?. So that i can show my team as well ?.

[nk_aspire]

those are date and time columns merged together using "+" =date+time.

[Pete_UK]

You can put this formula in C1:

=IF(ABS(A1-B1)>=TIME(0,5,0),TEXT(ABS(A1-B1)*24*60,"0")-5&" min out of +/- 5 minutes window","within window")

then copy down.

Hope this helps.

Pete