How To Check If Time Is Greater Than Or Less Than A Specific Time In Excel?

"Lost time is never found again."

Many organisations and professionals are looking for the best way to make the most of their time. Microsoft Excel has the necessary tools and functions to measure time quantitatively. Using an example dataset, we will demonstrate how to measure time against a specific time in Excel using two formulas mentioned in the steps.

Find out how in the following steps.

Method 1: Measuring Time with a Logical Function

We use an example of 9 students who had to submit their assignments before 03:30 PM. Their submission time is recorded in the spreadsheet below. In the first method, we use the "IF" function, a logical function also known as a Boolean operator. A logical function is used to compare two values in an Excel spreadsheet.

Step 1 − Select the cell where you want to see if the time mentioned in the row is greater or lesser than a specific time.

Step 2 − In the active cell, enter the formula. As we are measuring the time in cell B2 with a logical test in cell C2, if it is greater than 03:30 PM, then it is "yes"; otherwise, it is "no".

Excel formula to test whether a time exceeds a specific time or not: =IF(B2="","",IF(B2>(- -"3:30 PM"),"Yes","No"))

Step 3 − Press Control + Shift + Enter to conclude the test. When a set of { } appear around the array formula in the formula box, the logic test has been successfully completed.

Step 4− Replicate the formula in the rest of the table by dragging down the green box to the end of the table.

The final results will appear like this . . .

Method 2: Advanced Method to Measure Time Against Specific Time

In a logical test, you can modify the text values to get a detailed analysis of your data. This advanced method allows you to place customised values based on your information.

Step 1 − To measure time values, select one cell next to the column containing them. In this case, select cell C2 to measure the time value in cell B2.

Step 2 − For checking if a candidate is "late" or "on time" at 3:30 PM, enter the formula below

=IF(B2<TIME(15,30,0),"On time", IF(B2>=TIME(15,30,0),"Late","0"))

Press Control + Shift + Enter to execute the array formula.

Step 3 − In less than 2 seconds, you can check for each time value in column B if it is greater than or less than a particular time. Click and drag the green box at the bottom right corner of cell C2, where the formula was first entered.

Following this step, you can see which students had submitted their assignments "late" or "on time".

Conclusion

Using these two formulas, you can run logical tests on time values to determine whether they are greater or lower than the specified time. Microsoft Excel is a convenient platform to help make your data analysis easy and make the most of your productive hours. Accelerate your career with informative courses today!