Return Value If Time Is Greater Than and Less Than a Range in Excel: 6 Methods

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.

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

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.

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.

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.

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.