Excel If Time Between Range (4 Quick Ways)

Finding out if time is between range is used to make decisions for attendance tracking, event scheduling, project monitoring tasks, employee shift scheduling, and more. We’ll use the following basic dataset to showcase how to determine if a time value falls within a certain range.

Download the Practice Workbook

How to Check If a Time Is Between Range

We have a dataset of Employee Attendance records, which contains an employee’s entry time for several days. In the company policy (dataset on the right), the joining time is 10 AM, and joining until 10:30 AM is somewhat acceptable, but joining after that is considered late. We will determine when the employee was on time or late.

Method 1 – Use a Nested IF to Check If a Time Is Between Range

• Select a cell adjacent to the entered data (here that’s D5).
• Enter the formula below and drag the Fill Handle down.

Method 2 – Check If a Time Is Between Range by Applying the IFS function

• Select cell D5, insert the following formula, and drag down the Fill Handle.

Method 3 – Use the VLOOKUP Function to Find If a Time Is Between Range

• Insert the following formula in cell D5, then drag the Fill Handle down to AutoFill the column.

Here, C5 is the lookup value, $F$5:$G$7 is a range to lookup, and 2 is the column index of the value to return.

Method 4 – Find If a Time Is After a Constant Value Using the XLOOKUP Function

• Use the following formula:

Here, C5 is the lookup value, $F$5:$F$7 is the lookup array, $G$5:$G$7 is the return array, and -1 is to return “exact match or next smallest item”.

How to Find If a Time Is Between Two Times

We have a dataset containing a project’s starting, due, and ending times. We will determine if the project’s completion time is due.

Method 1 – Applying a Nested IF Function

• Copy this formula and paste it into cell D5, then drag the Fill Handle down to the last entry.

We used a nested If function to determine whether the project was completed in due time.

Method 2 – Combining IF and AND Functions 

• Insert the following formula in cell D5, then drag the Fill Handle down until you reach the last entry.

Click the image to see the full view.

Method 3 – Using the Median and Max Functions

• Select cell D5 and paste the following formula, then AutoFill the column.

The IF function checks if the value in D5 is the median of values B5, C5, and D5 and returns “On time” if true, or “Late” otherwise.

Frequently Asked Questions

What are the 3 arguments of the IF function?

The IF function in Microsoft Excel has three parts (arguments):

logical_test: compares something, such as a cell’s value.

value_if_true: Describe what should occur if the test result is TRUE.

value_if_false: Define what should occur if the test result is FALSE.

How do you put 3 conditions in the IF function in Excel?

You can put 3 conditions in the IF function using a nested IF function, with AND and OR logic. Here are some examples of them.

Nested IF: IF(C5<10, “Bad”, IF(C5<25, “OK”, IF(C5<30, “Good”, “Great”))

With AND Logic: IF(AND(A5=”Mav”, B5=”Jun”, C5>25), “Yes”, “No”)

With OR Logic: IF(OR(A5=”Mav”, B5=”Jun”, C5>25), “Yes”, “No”)

What is Timevalue in Excel?

Time value is a decimal number used to represent time values, which comprise a portion of date values (for instance, 12:00 PM is denoted by the number 0.5 since it is a half-day value).

Excel If Time Between Range: Knowledge Hub

<< Go Back to Formula List | Learn Excel