Microsoft Excel offers ways to work with dates and times as data points. While analyzing data, we often need to distinguish data points from the rest based on conditions such as larger or smaller than a specific value or within a range. We can also do that for time values in Excel too. In this article, we will be going over how to return a specific value if time is between a specific range in Excel.

## How to Return Expected Value If Time Is Between Range in Excel: 8 Suitable Examples

The main dataset that we are going to use for the demonstration is the one below.

In every case, we are going to put in different remarks based on the submission time. The range can be different for each example as different functions perform in different scenarios. Nevertheless, they all end up with the same result- different return values based on different ranges. Follow the methods below to find which suits your criteria.

### 1. Using IF Function

Let’s look at the following criteria.

It simply has 2- if it is submitted before 11:00 AM, it is on time, else the submission is late. We will start right off the bat with **the IF function**.

This function takes in three arguments- a condition, a value if the condition is true, and a value if the condition is false. It returns the value based on the outcome of the condition.

Follow these steps to find out how we can use this function to return value if time is between a specific range (before or after 11) in Excel.

**Steps:**

- First of all, select the cell you want to put the value in (cell
**C5**). - Then write down the following formula in it.

`=IF(B5<=$F$5,$E$5,$E$6)`

- After that, press
**Enter**on your keyboard.

- Now select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with this formula.

This way you can return a value based on the time range in Excel using only the **IF **function.

### 2. Utilizing Nested IF Functions

We can interloop **the IF function** to take more ranges and more outputs into consideration. For example, let’s say we have more conditions for the same dataset.

We can nest the **IF **function to insert these different conditions. As said before, this function takes in three arguments- a condition, a value if the condition is true, and a value if the condition is false. It returns the value based on the outcome of the condition.

Follow these steps to see how we can use the nested **IF **functions to return a specific value if the time is in a specific range in Excel.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula in it.

`=IF(B5<=$F$5,$E$5,IF(B5<=$F$6,$E$6,$E$7))`

- After that, press
**Enter**. Thus, you will have the return for the first value.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

This way you can use the nested **IF **formula to achieve the same result.

**🔎 Breakdown of the Formula**

**IF(B5<=$F$5,$E$5,IF(B5<=$F$6,$E$6,$E$7))**

👉 **IF(B5<=$F$5,$E$5,…) **checks whether the value of cell **B5 **is smaller or equal to that of **F5**. If it is smaller, then it returns the value of cell **E5 **else it moves on to the next portion of the formula.

👉 **IF(B5<=$F$6,$E$6,$E$7) **checks whether the value of cell **B5 **is smaller or equal to that of **F6**. If it is smaller, then it returns the value of cell **E6 **else it returns the value of cell **E7**.

👉 In this case, the first formula returns **TRUE**. So it doesn’t need to bother with the second formula and returns “On time” which is the value of cell **E5**.

### 3. Applying IFS Function

There is another function called **the IFS function** which can also be used to achieve the same result from the same dataset with the same criteria. But this function is only available in Excel 2019 and 365. This function takes multiple conditions and values and returns the corresponding value to the first **TRUE **condition.

Follow these steps to see how you can use this function to return a specific value if time is between a specific range in Excel.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula in it.

`=IFS(B5<=$F$5,$E$5,B5<=$F$6,$E$6,TRUE,$E$7)`

- Now press
**Enter**.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula.

Thus, you can achieve the same result as before.

### 4. Combining IF with AND Functions

Next up, we are going to use a combination of the **IF** and **AND** functions to form a formula that can return a specific value if the time is between a specific range on the Excel spreadsheet. But to do that, we first need to rework the conditions in the following way.

As said before, the **IF** function takes in three arguments- a condition, a value if the condition is true, and a value if the condition is false. It returns the value based on the outcome of the condition.

Meanwhile, the **AND **function checks whether all the arguments are **TRUE**, and returns **TRUE **if all arguments are **TRUE**.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula.

`=IF(AND(B5>=$F$5,B5<=$F$6),"On time","Late")`

**🔎 Breakdown of the Formula**

**IF(AND(B5>=$F$5,B5<=$F$6),”On time”,”Late”)**

👉 There are two conditions in **AND(B5>=$F$5,B5<=$F$6)**. The first one is whether the value of cell **B5 **is greater or equal to **F5**. The second one is whether the same value is smaller or equal to cell **F6**. If both conditions are true, then it returns **TRUE**, else it returns **FALSE**.

👉 Finally, the **IF(AND(B5>=$F$5,B5<=$F$6),”On time”,”Late”) **function returns “On time” if both the condition in the **AND **function is true, else it returns “Late”.

- After that, press
**Enter**on your keyboard.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

This is another way to return a specific value if the time is between a specific range in Excel.

### 5. Utilizing IF with MEDIAN Function

There is another way to achieve the same result with the same criteria on the same dataset. This time, we are going to use a combination of the **IF** and **MEDIAN** functions to construct the formula.

In it, the **IF** function takes in three arguments- a condition, a value if the condition is true, and a value if the condition is false. It returns the value based on the outcome of the condition. Meanwhile, the **MEDIAN **function returns the median of a group of numbers. That is actually the number in the middle of a set of numbers.

Follow these steps to see how we can achieve the result with the combination of these functions.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula.

`=IF(B5=MEDIAN($F$6,$F$5,B5),"On time","Late")`

**🔎 Breakdown of the Formula**

**IF(B5=MEDIAN($F$6,$F$5,B5),”On time”,”Late”)**

👉 First, **MEDIAN($F$6,$F$5,B5) **portion of the formula determines the median between the cell values of **F6, F5, **and **B5**.

👉 Then **IF(B5=MEDIAN($F$6,$F$5,B5),”On time”,”Late”) **checks whether the value of cell **B5 **is equal to the median or not. If it is, then the function prints “On time”. Otherwise, it returns the string “Late”.

- After that, press
**Enter**.

- Then select the cell again and click and drag the fill handle icon to the end of the column.

This is how you can use the combination of the **IF **and **MEDIAN **function to return a value based on the range a time is in between in Excel.

### 6. Utilizing IF, MIN and MAX Functions

Another way to achieve the same result for the same scenarios is to use a combination of the **IF**, **AND**, **MIN**, and **MAX** functions.

The **IF** function takes in three arguments- a condition, a value if the condition is true, and a value if the condition is false. It returns the value based on the outcome of the condition. Whereas the **AND **function checks whether all the arguments are **TRUE**, and returns **TRUE **if all arguments are **TRUE**. The **MIN** function extracts the lowest or smallest value from a range of cells or cell references. Meanwhile, the **MAX **function extracts the highest or largest one from the range.

Follow these steps to see how you can use the combination and the breakdown.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula.

`=IF(AND(B5>=MIN($F$5:$F$6),B5<=MAX($F$5:$F$6)),"On time","Late")`

**🔎 Breakdown of the Formula**

**IF(AND(B5>=MIN($F$5:$F$6),B5<=MAX($F$5:$F$6)),”On time”,”Late”)**

`👉`

**MIN($F$5:$F$6 **determines the minimum value in the range **F5:F6**.

`👉`

Whereas **MAX($F$5:$F$6) **returns the maximum value in the range **F5:F6**.

`👉`

**AND(B5>=MIN($F$5:$F$6),B5<=MAX($F$5:$F$6)) **checks whether the value of cell **B5 **is both greater or equal to the minimum value of the range **F5:F6 **and lesser or equal to the maximum value of the range **F5:F6**. The function returns **TRUE **if both conditions are correct. Else, it returns **FALSE**.

`👉`

**IF(AND(B5>=MIN($F$5:$F$6),B5<=MAX($F$5:$F$6)),”On time”,”Late”) **takes in the previous function as the condition that returns a boolean result. The function finally returns “On time” or “Late” depending on whether the previous function returned **TRUE **or **FALSE**.

- After that, press
**Enter**on your keyboard.

- Now select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

### 7. Use of VLOOKUP Function

There is another function in Excel called **the VLOOKUP function** which we can utilize to return a specific value if the time is in between a specific range. The **VLOOKUP** function looks for a given value in the leftmost column of a given table and then returns a value in the same row from a specified column.

The usage of the **VLOOKUP **function is more suitable to use in a scenario like the following one.

Follow these steps to see how we can do that for this dataset.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula in it.

`=VLOOKUP(B5,$E$5:$F$7,2,TRUE)`

- Now press
**Enter**.

- Next, select the cell again. Then click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

This is how you can use the **VLOOKUP **function to directly return a value if a time is between a specific range in Excel.

### 8. Applying XLOOKUP Function

There is another function to directly return a value in Excel if a time is between a specific range. That is **the XLOOKUP function**. This function takes in three required arguments- the value to search for, the array or range to search, and the array or range to return. It can also take in some optional arguments.

Follow these steps to see how we can use the function for the dataset.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula.

`=XLOOKUP(B5,$E$5:$E$7,$F$5:$F$7,,-1)`

- After that, press
**Enter**on your keyboard.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

This is how we can achieve the same result, but this time with the **XLOOKUP **function.

**Download Practice Workbook**

You can download the workbook used for the demonstration from the download link below.

## Conclusion

These were all the methods we can use to return a value if a time is between a specific range in Excel. Hope you have grasped the usage and breakdowns of the formulas and can apply them to your datasets as well. I hope you found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

**<< Go Back to If Time Between Range | Formula List | Learn Excel**