Excel If Time Between Range (4 Quick Ways)

In this Excel tutorial, I will show you 4 different ways to check in Excel if time is between range. Nested IF, IFS, VLOOKUP, and XLOOKUP are some functions that you can use for checking if time is between ranges. I also showed you several combined formulas to find if time is between two times in Excel.
While preparing this article, we used Microsoft 365 for applying all operations, but they are also applicable in all Excel versions.

Finding out if time is between range is used to make decisions for attendance tracking, event scheduling, project monitoring tasks, employee shift scheduling etc.

Download Practice Workbook

You can download the practice workbook here.

How Many Methods Are Available to Check If Time Is Between Range?

You can avail 4 different methods to check if a given time remains within a range. You can use nested IF, IFS, VLOOKUP, and XLOOKUP functions in formulas to check if time is between ranges.

We have a dataset of Employee Attendance records, which contains the entry time of an employee for several days. According to the employee’s company joining time is 10 AM, and joining till 10:30 AM is somewhat acceptable, but joining after that is considered late.

Now we will try to determine which days this particular employee was late, acceptable late, or on time. So, let us explore different ways to determine if time is between a range.

1. How to Use Nested IF to Check If Time Is Between Range?

You can use a nested IF function to check if the employee’s entry time is between a range. Let’s see the steps.

• To do this, select a cell adjacent to the entered data
• Enter the formula below and drag the Fill Handle down till the last entry.

2. How to Check If Time Is Between Range Applying IFS function?

We will apply the IFS function to determine if the time is between a range. Since the IFS function applies more than one logic at a time, it is more convenient than the nested IF function. Let us see the application of this function in this case.

• Select cell D5, insert the formula, and drag down the Fill Handle.

How to Utilize VLOOKUP Function to Find If Time Is Between Range?

Now we will utilize the VLOOKUP function to determine whether the entry is between the defined range. VLOOKUP function finds a lookup up from a particular range by row.

• To use this function, insert the following formula in the cell D5. Then, holding the Fill Handle, drag it down to the last entry.

Here, C5 is the lookup value,$F$5:$G$7 is a range to lookup, and 2 is the column index of the value to return.

How to Find If Time Is Between Two Ranges Using XLOOKUP Function?

You can also find out if the time is in between the range using the XLOOKUP function. XLOOKUP function looks for a value from a table by row. So, we used this function to find if the entry time is between ranges.

To apply this function, select cell D5 and paste the formula. After that, drag the Fill Handle down till the last entry.

Here, C5 is the lookup value, $F$5:$F$7 is the lookup array, $G$5:$G$7 is the return array, and -1 is to return “exact match or next smaller item”.

How to Find If Time Is Between Two Times?

We will demonstrate how to find if the time is between two times. To find this, we have a dataset containing a project’s starting, due, and ending times. We will determine if the project’s completion time is on due time or over it.

1. Applying Nested IF Function

• To apply nested IF, copy the formula, then paste it into cell D5. Next, drag the Fill Handle down to the last entry.

Here, we used a nested If function to determine whether or not the project was completed in due time.

2. Combining IF and AND Functions 

• To apply the formula, choose cell D5 and paste it. Then, drag the Fill Handle down until you reach the last entry.

Click the image to see the full view.

3. Using Median and Max Functions

• Select cell D5 and paste the formula. After that, confidently drag the Fill Handle down until you’ve reached the last entry.

Here, the IF function checks the value in D5 is the median of values B5, C5, and D5 and returns “On time” if true otherwise, “Late”.

Which Things You Have to Remember?

• Your data must be formatted correctly for a time. Otherwise, a mistake might occur.
• Formatting your time in a 24-hour format is preferable. You wouldn’t have to worry about A.M./P.M. confusion, and making decisions would be more straightforward.
• Remember to apply the formulas according to your dataset.

Conclusion

This article demonstrated how to check if time is between ranges in Excel. You can use Excel’s IF function to analyze and manipulate data based on specific time ranges. Additionally, IFS, VLOOKUP, and XLOOKUP functions are also useful functions to check if time falls between ranges. You can use these methods for managing schedules, tracking deadlines, and automating tasks. Learning this article will help you to analyze data and make informed decisions based on time intervals.

Frequently Asked Questions

1. What are the 3 arguments of the IF function?

The IF function in Microsoft Excel has three parts (arguments):

logical_test: compares something, such as a cell’s value.

value_if_true: Describe what should occur if the test result is TRUE.

value_if_false: Define what should occur if the test result is FALSE.

2. How do you put 3 conditions in the IF function in Excel?

You can put 3 conditions in the IF function using a nested IF function, with AND and OR logic. Here are some examples of them.

Nested IF: IF(C5<10, “Bad”, IF(C5<25, “OK”, IF(C5<30, “Good”, “Great”))

With AND Logic: IF(AND(A5=”Mav”, B5=”Jun”, C5>25), “Yes”, “No”)

With OR Logic: IF(OR(A5=”Mav”, B5=”Jun”, C5>25), “Yes”, “No”)

3. What is Timevalue in Excel?

Time value is a decimal number used to represent time values, which comprise a portion of date values (for instance, 12:00 PM is denoted by the number 0.5 since it is a half-day value).

Excel If Time Between Range: Knowledge Hub

<< Go Back to Formula List | Learn Excel