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.

**Table of Contents**Expand

## 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.

`=IF(C5>$F$6,$G$7,(IF(C5>$F$5,$G$6,$G$5)))`

**Formula Breakdown**

**IF(C5>$F$5,$G$6,$G$5)=On Time:**This evaluates whether the value in cell**C5**is greater than**F5**. If it is true, it will return the cell value of**G6**, which is acceptable late; otherwise, it will return the value of cell**G5**.**IF(C5>$F$6,$G$7, On Time)= On Time:**This part checks if the value in cell**C5**is greater than the value in cell**F6**, and if it is true, it will return the “Late” value in cell**G7**. It is invalid in this case, so it returned “On Time.”

### 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**.

`=IFS(C5>$F$6,$G$7,C5>$F$5,$G$6,TRUE,$G$5)`

**Formula Breakdown**

**IF(C5>$F$5,$G$6,$G$5)=On Time:**This evaluates whether the value in cell**C5**is greater than**F5**. If it is true, it will return the cell value of**G6**, which is acceptable late; otherwise, it will return the value of cell**G5**.

**IF(C5>$F$6,$G$7, On Time)= On Time:**This part checks if the value in cell**C5**is greater than the value in cell**F6**, and if it is true, it will return the “Late” value in cell**G7**. It is invalid in this case, so it returned “On Time.”

### 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.

`=VLOOKUP(C5,$F$5:$G$7,2)`

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.

`=XLOOKUP(C5,$F$5:$F$7,$G$5:$G$7,,-1)`

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.

`=IF(D5>=B5,IF(D5<=C5, "On Time", "Late"),)`

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

**Formula Breakdown**

**IF(D5<=C5, “On Time”, “Late”)= On Time:**This part evaluates if the value in**D5**is less than or equal to**C5**and returns “On time” if true; otherwise, “Late.”**IF(D5>=B5,”On Time”,)= On Time:**This part checks if**D5**is greater than**B5**and returns “On time” or “Late” according to the next logical test.

__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.

`=IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),"On Time","Late")`

**Formula Breakdown**

**D5>=MIN(B5:C5)= TRUE:**This part checks if D5 is greater than the minimum value of the B5:C5 range.**D5<=MAX(B5:C5)= TRUE:**This part checks if D5 is lesser than the maximum value of B5:C5 range**IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),”On Time”,”Late”)= On Time:**Here, the IF function checks if the value is true for both the logic or not and returns “On Time” or “Late”.

__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.

`=IF(D5=MEDIAN(B5,C5,D5),"On Time","Late")`

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**