Table of Contents
The IF function in Excel allows users to apply a logical test to a cell or range of cells, and specify what action should be taken if the test is true or false. This function can also be used with time values in Excel, allowing users to perform calculations or actions based on specific time conditions. For example, the IF function can be used to check if a certain time value falls within a specific range, and then perform a calculation or display a result accordingly. This can be particularly useful for tracking and analyzing time-based data, such as project timelines or work schedules. By properly utilizing the IF function with time in Excel, users can effectively automate data analysis and decision-making processes.
Excel: Use an IF Function with Time
You can use the following formulas to create an IF function with times in Excel:
Method 1: Create IF Function to Compare Time in Cell with Specific Time
=IF(B2<=TIMEVALUE("9:00"), "Yes", "No")
This formula checks if the time in cell B2 is equal to or before 9:00 AM and returns “Yes” or “No” accordingly.
Method 2: Create IF Function to Compare Times in Two Cells
=IF(A2<=B2, "Yes", "No")
This formula checks if the time in cell B2 is equal to or before the time in cell C2 and returns “Yes” or “No” accordingly.
The following examples show how to use each formula in practice.
Example 1: IF Function to Compare Time in Cell with Specific Time
Suppose we have a list of times in Excel that show when some task was done and we want to know if each task was done by 9:00 AM:
We can type the following formula into cell C2 to return “Yes” if the task in cell B2 was done by 9:00 AM or return “No” otherwise:
=IF(B2<=TIMEVALUE("9:00"), "Yes", "No")
We can then drag and fill this formula down to each remaining cell in column C:
The formula returns either “Yes” or “No” depending on whether or not the time in column B is before or equal to 9:00 AM.
Note: The TIMEVALUE function in Excel converts a time stored as a text value to a time that is recognizable by Excel formulas.
Example 2: IF Function to Compare Times in Two Cells
Suppose we have two columns of times in Excel that show when some task was completed along with the task deadline:
We can type the following formula into cell C2 to return “Yes” if the task in cell A2 was done before or on the deadline time in cell B2 or to return “No” if it wasn’t:
=IF(A2<=B2, "Yes", "No")
We can then drag and fill this formula down to each remaining cell in column C:
The formula returns either “Yes” or “No” depending on whether or not the task in each row was completed by the deadline time.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: