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.

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.

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

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.

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.

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

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.

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

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

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

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

• Vlookup with Time Range in Excel (5 Easy Ways)