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
Next
```

Then, 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 Sub`

Finally, 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**