Table of Contents
To check if a given time falls between two specific times in Excel, you can use the “IF” function with logical operators. First, you need to convert the given time and the two specific times into a serial number using the “TIMEVALUE” function. Then, use the logical operators (greater than or equal to and less than or equal to) to compare the serial numbers. If the given time is between the two specific times, the function will return a “TRUE” value, otherwise it will return a “FALSE” value. This method can be helpful in various scenarios such as tracking work schedules, monitoring project timelines, or scheduling appointments.
Excel: Check if Time is Between Two Times
You can use the following formula in Excel to check if the time in a given cell is between two specific times:
=IF(AND(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"Yes","No")
This particular formula checks if the time in cell C2 is between the start time in cell A2 and the end time in cell B2, then returns either “Yes” or “No” as a result.
The following example shows how to use this formula in practice.
Example: Check if Time is Between Two Times in Excel
Suppose we have the following dataset in Excel that shows start times and end times along with the time that some specific event occurred:
Suppose we would like to determine if each event occurred between the start and end times in each row.
To do so, we can type the following formula into cell D2:
=IF(AND(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"Yes","No")
We can then drag and fill this formula down to each remaining cell in column D:
Column D now displays either “Yes” or “No” to indicate if each Event Time is within the Start Time and End Time for each row.
For example:
- The first event time of 7:54 AM is between the start time of 1:15 AM and 8:12 AM so column D returns Yes.
- The second event time of 10:40 AM is between the start time of 2:12 AM and 10:40 AM so column D returns Yes.
- The third event time of 5:18 AM is not between the start time of 3:15 AM and 5:15 AM so column D returns No.
And so on.
Note #1: If the event time is equal to either the start or end time, the function will return Yes since we used <= and >= symbols in our IF function.
Note #2:You could choose values other than “Yes” or “No” in the IF function if you’d like to return other values.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: