Return Value If Time Lies Between Multiple Time Ranges in Excel

Dates and times can be used as data points in Microsoft Excel. Analysis of data often involves distinguishing data points based on conditions such as larger or smaller than a specific value or within a range. Excel also allows us to do that for time values. If you are looking for special tricks to know how to deal with Excel if between multiple time ranges, you’ve come to the right place. This article discusses how to return a specific value in Excel if time falls within multiple time ranges. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


3 Methods to Return Expected Value If Time Lies Between Multiple Time Ranges in Excel

In the following section, we will use three effective and tricky methods to deal with Excel if between multiple time ranges. The three most effective methods are: nested IF functions, combining IF, IFERROR, and IFS functions, and embedding VBA code. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Using Nested IF Functions

Here, we demonstrate how to return a specific value if the time is in multiple ranges in Excel. The following dataset contains different task submission times and time ranges. In every case, we are going to put in different remarks based on the submission time. We use the nested IF functions to take more ranges and more outputs into consideration.

using Nested IF functions to Return Expected Value If Time Between Multiple Time Ranges in Excel

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

📌 Steps:

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

=IF(C5>$F$5,IF(C5<$G$5,$H$5),$H$6)

  • Next, press Enter.
  • Consequently, you will get the following remark in cell D5.

  • 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.
  • Therefore, you can return a value based on the time range in Excel using the nested IF function.

get the output

🔎 How Does the Formula Work? 

♣️ Formula: IF(C5>$F$5,IF(C5<$G$5,$H$5),$H$6)

👉 IF(C5>$F$5,…) checks whether the value of cell C5 is greater than F5. If it is greater than F5, it moves on to the next portion of the formula.

👉 IF(C5<$G$5,$H$5) checks whether the value of cell C5 is smaller than G5. If it is smaller, then it returns the value of cell H5, else it moves on to the next portion of the formula and returns the value of cell H6.

💡 Note:

  • Your data must be formatted in the proper Time format. It is possible for an error to occur if this is not done.
  • Formatting your time in 24-hour format is preferable. You wouldn’t have to worry about A.M./P.M. confusion, and making decisions would be simpler.

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


2. Combining IF, IFERROR and IFS Functions

The next method is to show another method of determining the value based on multiple ranges of time in Excel. Different task submission times and time ranges are included in the following dataset. Depending on the submission time, we will make different remarks. We will combine IF, IFERROR, and IFS functions in order to consider more ranges and outputs.

combining different functions to Return Expected Value If Time Between Multiple Time Ranges in Excel

To retrieve a specific value in Excel based on multiple ranges of time, follow these steps.

📌 Steps:

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

=IFERROR(IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)),$H$6)

  • Next, press Enter.
  • Consequently, you will get the following remark in cell D5.

get the remarks column

🔎 How Does the Formula Work?

♣️ Formula: IFERROR(IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)),$H$6)

👉 IFS(C5>=$F$5,…)

This formula checks whether the value of cell C5 is greater or equal to F5. If it is greater than F5, it moves on to the next portion of the formula.

👉 IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6

In the next portion, the formula will check whether the value of cell C5 is smaller or equal to G5. If it is smaller, then it returns the value of cell H5, else it moves on to the next portion of the formula and checks whether the value of cell C5 is greater or equal to F6.

👉 IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)

The formula will determine whether cell C5‘s value is less than or equal to cell G6 in the subsequent section if the previous condition is true. If it is smaller, then it returns the value of cell H6, else it moves on to the next portion of the formula.

👉 IFERROR(IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)),$H$6)

Finally, the IFERROR function will catch the error and replace it with the value of cell H6.

Read More: Return Value If Time Is Greater Than and Less Than a Range in Excel


3. Embedding VBA Code

If you want to return a specific value if the time is in multiple ranges in Excel, you need to use the help of VBA. The Microsoft Visual Basic for Applications (VBA) programming language is Microsoft’s event-driven programming language. It is necessary to have the Developer tab on your ribbon in order to use this feature. Click here to see how you can show the Developer tab on your ribbon. Once you have that, follow these detailed steps to return a specific value if the time is in multiple ranges in Excel.

embedding VBA code to Return Expected Value If Time Between Multiple Time Ranges in Excel

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. Firstly, to open the VBA window, go to the Developer tab on your ribbon. Then, select Visual Basic from the Code group.

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. Firstly, to insert a module for the code, go to the Insert tab on the VBA editor. Then, click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub Multiple_Range()
For Each main_cell In Selection
If main_cell.Value > Range("F5").Value And main_cell.Value < Range("G5").Value Then
    main_cell.Offset(0, 1) = Range("H5").Value
Else
    main_cell.Offset(0, 1) = Range("H6").Value
   
End If
Next
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window.
  • Then, select the range of the cells C5:C12.
  • After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

⧭ Output:

  • This way, you can return a value based on the time range in Excel like in the below figure.

get the desired remarks column If Time Between Multiple Time Ranges in Excel

🔎 VBA Code Explanation:

Sub Multiple_Range()

First of all, provide a name for the sub-procedure of the macro.

For Each main_cell In Selection
If main_cell.Value > Range("F5").Value And main_cell.Value < Range("G5").Value Then
    main_cell.Offset(0, 1) = Range("H5").Value
Else
    main_cell.Offset(0, 1) = Range("H6").Value
End If
Next

In the first line of this piece of the code, we inserted the For loop and IF condition. Afterward, by using the IF formula, we will check whether the selected cell is greater than cell F6 and whether it is less than cell G5. If the condition is met, the function will return the value of cell H5. Otherwise, it returns the value of cell H6.

End Sub

Finally, end the sub-procedure of the macro.

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


Conclusion

That’s the end of today’s session. I strongly believe that from now, you may learn how to deal with Excel if between multiple time ranges. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo