How To Check If Time Is Between Two Times In Excel?


MS Excel is handy when you need to keep track of your time and make the most of your resources. Suppose you want to find out how long it takes employees to complete an assignment. You can log the times in your spreadsheet and run a simple function to check if resources are being optimised or not.

In light of the results, you can highlight which reports were submitted after the deadline. With two formulas, this tutorial demonstrates how to determine whether a specific time is within a particular range.

Using the Nested “IF” Function

A logical function, the 'IF' function in Microsoft Excel, is used for situations where a condition is either determined as TRUE or FALSE. Using the example spreadsheet, we demonstrate how to use the function to check if the time is between two times in excel.

Step 1 − Select an empty cell containing the time range information in the row. In this case, click on cell D2 in Row 2, which includes time information from cells A2 through B2.


Step 2 − Enter the formula in the active cell to check if the specified deadline time is within the two specified times.

Excel Formula to check if time is between two selected time ranges: =IF(C2>=A2,IF(C2<=B2, "In Range", "Out of Range"), "Out of Range")


Step 3 − Press Control + Shift + Enter after entering the formula. When the formula is entered correctly, a set of curly brackets { } appears around it in the formula box. After this step, you will see the result.


Step 4 − If you want to replicate the results for the rest of the table, drag the green box in active cell D2 to the end of the table.


Your final results will appear like this . . .


Using a Combination Of “IF-AND” Formula

It is common for Excel professionals to combine the IF and AND formulas when they are checking multiple conditions simultaneously. When you use the AND formula, you can specify what you should get if all the conditions are true and what you should get if any are false.

Using the same example data set, let's demonstrate the formula.

Step 1 − Select a cell next to the row containing the time information you want to check and enter the formula.


Step 2 − After entering the formula mentioned below, press Control + Shift + Enter.

IF and AND formula to check if specified time is within the selected range: =IF(AND(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"YES","NO")

In the formula above, C2 is the specific time you want to check, and A2 and B2 are the cells containing the two times you want to check.


Step 3 − Drag the green box down to the cells you want to fill. When the times are within the specified period, a Yes will be displayed; if not, a No will be displayed.


Conclusion

Using the two array formulas mentioned above, you can check if the specified time is between the two times. It is also possible to run the formula on dates. Make sure the time and date should be in the same format when comparing in Excel. You can find more useful tutorials in our courses.

Updated on: 20-Sep-2022

21K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements