Sometimes, we need to find remarks if time is greater than and less than in Excel. In that case, there are starting and closing times. We need to find if the time is greater than starting time and less than the closing time. This article will show how if time is greater than and less than in Excel. I hope you find this article informative and gain lots of knowledge.
How to Return Value If Time Is Greater Than and Less Than a Range in Excel: 6 Suitable Methods
To show if time is greater than and less than in Excel, we have found six different and effective methods including several Excel functions and VBA code. All of these are really effective to use. In this article, we will utilize the Nested IF, VLOOKUP, and XLOOKUP functions. By using these functions, we would like to verify if time is greater than and less than in Excel.
1. Using Nested IF Functions
Our first method is based on using the Nested IF functions. In this method, we would like to utilize several IF functions to get the desired result. To show this method, we take a dataset that includes several submission times and start and end times. Using those start and end times, we would like to create remarks on those submission times. Next, follow the steps carefully.
Steps
- First, select cell D5.
- Then, write down the following formula in the formula box.
=IF(C5>$F$5,IF(C5<$G$5,$H$5),$H$6)
- Press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula:
⇒ IF(C5>$F$5,IF(C5<$G$5,$H$5),$H$6): The IF function verifies a condition and if it meets the condition then it returns a value. Otherwise, it returns another value.  Here, we utilize nested IF functions. First, it looks at if the submission time( C5) is greater than the start time (F5). If it is true, then go to the next IF condition where the submission time (C5) is less than the end time(G5). Then, it returns remarks as On time. Otherwise, it returns remarks as Late.
2. Combination of IFS and IFERROR Functions
Our second method is based on the combination of IFS and IFERROR functions. In this method, we would like to utilize both of them to get the desired result. Here, we utilize the both start and end time to establish remarks. To understand the method, follow the steps.
Steps
- First, select cell D5.
- Then, write down the following formula in the formula box.
=IFERROR(IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)),$H$6)
- Press Ctrl+Shift+Enter to apply the formula instead of only Enter. Because this is an array formula.
- After that, drag the Fill Handle icon down the column.
🔎 Breakdown 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)
⇒ IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)): The IFS function takes multiple conditions and values, and returns the corresponding value to the first true condition. Here, the first condition is if the submission time (C5) is greater than or equal to the start time (F5). Then, it goes to another IF condition where the submission time (C5) is less than or equal to the end time (G5). It returns remarks On time (H5). Then, the second condition of the IFS function is if the submission time (C5) is greater than or equal to the start time (F6). Then, it goes to another IF condition where the submission time (C5) is less than or equal to the end time (G6). It returns remarks Late (H6).
 ⇒ IFERROR(IFS(C5>=$F$5,IF(C5<=$G$5,$H$5),C5>=$F$6,IF(C5<=$G$6,$H$6)),$H$6): when the IFS function returns an error, it will take it in the IFERROR function and it returns remarks Late (H6).
3. Combining IF with AND Function
Our third method is based on the combination of IF with AND function. In this method, we would like to see whether the arrival time is on time or late. To show this example, we take a dataset that includes several entry times and two conditions. Follow the steps to understand it properly.
Steps
- First, select cell C5.
- Then, write down the following formula in the formula box.
=IF(AND(B5>=$F$4,B5<=$F$5),$E$5,$E$4)
- Press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula:
 IF(AND(B5>=$F$4,B5<=$F$5),$E$5,$E$4): the IF function takes the criteria and verifies if it is true or not. If the criterion is true, it will return a certain value. Otherwise, it will return another value. Here, by using the AND function, we set two different criteria. If the entry time (B5) is greater than or equal to F4 and the entry time (B5) is less than or equal to F5, then the IF function returns the arrival as On-time. Otherwise, it returns the arrival as Late.
4. Embedding VBA Code
To show if time is greater than and less than in Excel, we can use the Excel VBA code. In this VBA code, we take two different conditions. If the cell meets both conditions, it will return a result. Otherwise, it will return another result. In this method, we will include both the VBA code and its explanation. Before doing anything, enable the Developer tab on the ribbon. Then, follow the steps.
Steps
- First, go to the Developer tab on the ribbon.
- Then, select the Visual Basic option from the Code group.
- It will open up the Visual Basic window.
- Then, go to the Insert tab at the top.
- After that, select the Module option.
- As a result, a Module code window will appear.
- Write down the following code.
Sub Excel_If_Time_is_Greater_Than_and_Less_Than()
For Each cel In Selection
If cel.Value > Range("F5").Value And cel.Value < Range("G5").Value Then
cel.Offset(0, 1) = Range("H5").Value
Else
cel.Offset(0, 1) = Range("H6").Value
End If
Next
End Sub- Then, close the Visual Basic window.
- After that, select the range of cells C5 to C12.
- Then, go to the Developer tab on the ribbon.
- Select the Macros option from the Code group.
- Then, the Macro dialog box will appear.
- Select Excel_If_Time_is_Greater_Than_and_Less_Than from the Macro name section.
- After that, click on Run.
- As a result, the remarks section will be filled up by using those conditions. See the screenshot.
🔎 VBA Code Explanation:
Sub Excel_If_Time_is_Greater_Than_and_Less_Than()First of all, provide a name for the sub-procedure of the macro
For Each cel In Selection
If cel.Value > Range("F5").Value And cel.Value < Range("G5").Value Then
cel.Offset(0, 1) = Range("H5").Value
Else
cel.Offset(0, 1) = Range("H6").Value
End If
NextThen, take a For Each loop. After that, apply the If statement under that For Each loop. If the cell value is greater than cell range F5 (Start Time) and less than cell range G5 (End Time). Then, it returns remarks On time (H5). Otherwise, it will return remarks Late (H6).
End SubFinally, end the sub-procedure of the macro.
5. Utilizing VLOOKUP Function
Next, we can utilize the VLOOKUP function to show if time is greater than and less than in Excel. In this method, we use the VLOOKUP function and find the remarks for a given condition. To understand the method, follow the steps.
Steps
- First, select cell C5.
- Then, write down the following formula in the formula box.
=VLOOKUP(B5,$E$4:$F$6,2,TRUE)
- Press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula:
VLOOKUP(B5,$E$4:$F$6,2,TRUE): The VLOOKUP function looks at a value in a given range and returns a value that meets all the criteria. Here, we set the lookup value B5 which is the entry time. Then, set the lookup table array cell E4 to cell F6 from where it looks at the given value. After that, define the column number. Then, set the value for an approximate match and define it as true. The VLOOKUP function will return the value from the second column of the given array for the corresponding lookup value.
6. Using XLOOKUP Function
Finally, we can utilize the XLOOKUP function to show if time is greater than and less than in Excel. In this method, we use the XLOOKUP function and find the remarks for a given condition. To understand the method, follow the steps.
Steps
- First, select cell C5.
- Then, write down the following formula in the formula box.
=XLOOKUP(B5,$E$4:$E$6,$F$4:$F$6,,-1)
- Press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula:
XLOOKUP(B5,$E$4:$E$6,$F$4:$F$6,,-1): The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. Here, set the lookup value B5 (Entry Time). Then, set the lookup array from where it will search the lookup value. After that, set a return array from where it searches the corresponding item that matches the lookup value. Then, we can add “if_not_found“. We set it as blank. After that, you can also add match type. Here, -1 denotes the exact or next smaller item. Finally, the XLOOKUP function returns the arrival as On time.
Things to Remember
- The method having the XLOOKUP function can only be available for Microsoft 365. You can’t get it in other Excel versions. So. keep it in your mind.
- The method having IFS and IFERROR functions produces an array formula. In that case, to apply the formula, you have to press Ctrl+Shift+Enter rather than only Enter.
Download Practice Workbook
Download the practice workbook below.
Conclusion
We have shown six different and effective methods to show if time is greater than and less than in Excel. In this article, we utilize several Excel functions and VBA code. All of these methods are equally effective. I hope we covered all possible areas on this topic. If you have further questions, feel free to ask in the comment box.
<< Go Back to If Time Between Range | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
