### Method 1 – Using Nested IF Functions

**Steps**

- Select cell
**D5**. - 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.

- 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; it returns another value. **Â **We utilize nested **IF** functions. It looks at if the submission time**( C5)** is greater than the start time **(F5)**. If true, go to the next IF condition where the submission time **(C5)** is less than the end time(**G5)**. It returns remarks as **On time**. Otherwise, it returns remarks as** Late**.

### Method 2 – Combination of IFS and IFERROR Functions

**Steps**

- Select cell
**D5**. - 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.

- 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. The first condition is if the submission time **(C5) **is greater than or equal to the start time **(F5)**. 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)**. The second condition of the **IFS** function is if the submission time **(C5) **is greater than or equal to the start time **(F6)**. 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)**.

### Method 3 – Combining IF with AND Function

**Steps**

- Select cell
**C5**. - 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.

** **

- 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. It will return another value. 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**.

### Method 4 – Embedding VBA Code

**Steps**

- Go to the
**Developer**tab on the ribbon. - Select the
**Visual Basic**option from the**CodeÂ**group.

- It will open up the
**Visual BasicÂ**window. - Go to the
**Insert**tab at the top. - Select the
**ModuleÂ**option.

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

- Close the
**Visual BasicÂ**window. - Select the range of cells
**C5**to**C12**.

- Go to the
**Developer**tab on the ribbon. - Select the
**Macros**option from the**CodeÂ**group.

- The
**Macro**dialog box will appear. - Select
**Excel_If_Time_is_Greater_Than_and_Less_Than**from the**Macro nameÂ**section. - Click
**Run**.

** **

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

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

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). It returns remarks On time** (H5)**. Otherwise, it will return remarks Late **(H6)**.

`End Sub`

Finally, end the sub-procedure of the macro.

### Method 5 – Utilizing VLOOKUP Function

**Steps**

- Select cell
**C5**. - Write down the following formula in the formula box.

`=VLOOKUP(B5,$E$4:$F$6,2,TRUE)`

- Press
**Enter**to apply the formula.

** **

- 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. We set the lookup value **B5** which is the entry time. Set the lookup table array cell **E4** to cell **F6** from where it looks at the given value. Define the column number. 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.

### Method 6 – Using XLOOKUP Function

**Steps**

- Select cell
**C5**. - 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.

** **

- 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. Set the lookup value **B5** (Entry Time). Set the lookup array from where it will search the lookup value. Set a return array from where it searches the corresponding item that matches the lookup value. Add â€œ**if_not_foundâ€œ**. We set it as blank. 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.

**<< Go Back to If Time Between Range | Formula List | Learn Excel**