Excel: Check if Time is Between Two Times

Understanding the Core Formula Components

To determine if a target time is inclusive of a set range, we combine several logical functions. The simplest way to structure this check in Excel, while accounting for potential reversed entry of start and end times, is to use the following structure:

=IF(AND(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"Yes","No")

This specific formula checks if the time in cell C2 (the target time) is greater than or equal to the minimum chronological boundary (derived from A2 and B2), AND simultaneously less than or equal to the maximum chronological boundary. The result is a simple boolean output: “Yes” if the time is within the boundaries, or “No” if it is outside.

We will now walk through a practical implementation to illustrate how this formula operates with real data.

Practical Example: Validating Event Timings

Suppose you maintain a dataset tracking various events, requiring verification that each event occurred within its designated time slot. Our dataset includes columns for Start Time, End Time, and the actual Event Time.

Our objective is to populate a new column (D) with a status indicating whether the actual event occurred between the prescribed start and end times for that row.

Step-by-Step Implementation of the Formula

We begin by applying the formula to the first row of data, specifically in cell D2. This calculation will evaluate the event time in C2 against the boundaries set in A2 and B2.

=IF(AND(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"Yes","No")

After entering the formula into D2 and pressing Enter, we utilize the fill handle to drag the formula down. This action ensures that the relative cell references update automatically, applying the logic to every subsequent row in the dataset.

Column D now clearly displays either “Yes” or “No”, providing immediate verification of whether each Event Time successfully fell within its respective Start Time and End Time range.

Analyzing Specific Boundary Cases

Reviewing the results confirms the accurate application of the logical check, especially concerning events occurring directly on the boundary lines:

• The first event time (7:54 AM) is chronologically between the start time (1:15 AM) and end time (8:12 AM). Column D confirms compliance by returning Yes.
• The second event time (10:40 AM) precisely matches the end time (10:40 AM). Because we employed the inclusive operator (<=), the result in column D is also Yes.
• The third event time (5:18 AM) occurs after the boundary range (3:15 AM to 5:15 AM). As it violates the upper limit condition, column D correctly returns No.

Technical Deep Dive: Why MIN, MAX, IF, and AND are Essential

The robustness of this solution lies in the strategic combination of functions. The IF function serves as the final decision maker, while the nested AND function ensures that both criteria (greater than minimum AND less than maximum) are met simultaneously.

Crucially, the MIN and MAX functions provide flexibility. By wrapping the boundary cells (A2:B2) within these functions, we guarantee that the comparison always uses the earliest time as the lower limit and the latest time as the upper limit, regardless of which cell (A2 or B2) contains the start or end time. This makes the formula error-proof against entry order mistakes.

Customizing Output and Exclusivity

You retain full control over the output values. While “Yes” and “No” are defaults, you can easily substitute them with meaningful status indicators, such as “On Time” or “Late,” or even numerical values for further calculation, simply by editing the final two arguments of the IF function.

Furthermore, if your scheduling requires that the boundaries themselves must be excluded (e.g., an event cannot occur exactly at 8:00 AM or 5:00 PM), you must switch from the inclusive operators (>= and <=) to the exclusive operators (> and <) within the AND function structure.