Excel If Time Is Between Range Then Return Value

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.


Download Practice Workbook

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


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

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.

returning value if time is between range in excel

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)

if formula to return value for time in a range in excel

  • After that, press Enter on your keyboard.

result of the if formula

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

Read More: Check If Time Is Between Two Times in Excel (2 Suitable Ways)


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

nested if formula for returning value of time in range in excel

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

autofilling nested if formula

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)

nested ifs formula for returning value of time in a range in excel

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

auto filling ifs 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")

combination of if and and function to return value for time in a range excel

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

auto filling formula for if and and functions

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

if and median formula to return value in time range in excel

  • After that, press Enter.

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

auto filling formula of if and median function

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")

if, min. and max functions for returning value if time is in a range

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

auto filling combination of if, min, and max functions

Read More: Return Expected Value If Time Is Greater Than 1 Hour in Excel


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)

vlookup function to return value if time is in between range in excel

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

auto filling vlookup function

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

Read More: Vlookup with Time Range in Excel (5 Easy Ways)


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)

xlookup function to return value if time is between range in excel

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

auto filling xlookup function

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


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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo