Finding out if time is between range is used to make decisions for attendance tracking, event scheduling, project monitoring tasks, employee shift scheduling, and more. We’ll use the following basic dataset to showcase how to determine if a time value falls within a certain range.

**Download the Practice Workbook**

## How to Check If a Time Is Between Range

We have a dataset of Employee Attendance records, which contains an employee’s entry time for several days. In the company policy (dataset on the right), the joining time is 10 AM, and joining until 10:30 AM is somewhat acceptable, but joining after that is considered late. We will determine when the employee was on time or late.

### Method 1 – Use a Nested IF to Check If a Time Is Between Range

- Select a cell adjacent to the entered data (here that’s D5).
- Enter the formula below and drag the
**Fill Handle**down.

`=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.”

### Method 2 – Check If a Time Is Between Range by Applying the IFS function

- Select cell
**D5**, insert the following 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.”

### Method 3 – Use the VLOOKUP Function to Find If a Time Is Between Range

- Insert the following formula in cell
**D5**, then drag the Fill Handle down to AutoFill the column.

`=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.

### Method 4 – Find If a Time Is After a Constant Value Using the XLOOKUP Function

- Use the following formula:

`=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 smallest item”.

## How to Find If a Time Is Between Two Times

We have a dataset containing a project’s starting, due, and ending times. We will determine if the project’s completion time is due.

__Method 1 – Applying a Nested IF Function__

- Copy this formula and paste it into cell
**D5,**then drag the**Fill Handle**down to the last entry.

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

We used a nested If function to determine whether 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.

__Method 2 – Combining IF and AND Functions __

- Insert the following formula in cell
**D5**, 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”.

__Method 3 – Using the Median and Max Functions__

- Select cell
**D5**and paste the following formula, then AutoFill the column.

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

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

## Frequently Asked Questions

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

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

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