CODEWITHSUNDEEP
excel
HN_seeker
HN_seeker

Reputation: 27

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

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

For e.g. 

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

A1 here time of collection of data, and B1 is the scheduled time. The acceptable window or range for this is +/- 5 minutes out of window. 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 as above which can account for + 5 minutes and - 5 minutes scenarios?.

Thanks in advance.

Upvotes: 0

Views: 198

Answers (2)

user4039065
user4039065

Reputation:

  1. Use this formula¹ in C1 and fill down as necessary,

    =ROUND(MAX((ABS(A1-B1)-0.05)*100, 0), 0)

  2. Use the following custom number format on column C,

    0 \mi\n out of ± 5 \mi\nut\e wi\n\dow

The advantages of a custom number format is that the numbers remain numbers and are available for future calculations (e.g. SUM, AVERAGE, etc) while displaying the text you wish to show. Note the right-alignment in the cells indicating true numbers.

enter image description here

¹ This formula depends on the time values being mixed numbers and not true time. If the time values are true time with a non-EN-US number format then additional maths may have to be applied to the formula.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

=IF(ABS(A1-B1)>(5/1440),"Problem!","OK")

5/1440 is 5 minutes (1440 minutes in a full day)

Upvotes: 1

Related Questions