How To Check If The Time Difference Is Greater Than A Specific Time?

MS ExcelMicrosoft TechnologiesAdvanced Excel Function

Microsoft Excel has the potential to make your professional life easy. It helps you organise, evaluate, and calculate data reliably. Managers and administrators can make relevant decisions that benefit their organisations using the various tools and functions available on the software.

With Excel, you can also check the time difference over a specific period. Using this simple method in this tutorial, you can determine if a time difference is greater than a particular period in any given situation.

Comparing Time Difference with a Specific Duration Using an Array Formula

Suppose you maintain an employee log-in sheet and must determine which employees have worked more than eight hours. As a result, we need to apply an array formula with the instruction that the time difference is greater than 8 hours.

Step 1 − Select an empty cell next, preferably to the one you want to check for the time difference.


Step 2 − To check the time difference, enter this formula in the active cell with the instruction: if the gap between B2 and C2 is greater than 8 hours, it will produce a True result; otherwise, a False result.

Formula to check Time Difference Greater than 8 hours −

=IF(C2 > B2 + TIME(8,0,0),TRUE,FALSE)


Step 3 − Since it is an array formula, you need to press Control + Shift + Enter to complete the function. If you use a macOS keyboard, press Command + Shift + Enter. Your result will appear after this step.


Step 4 − You can repeat the formula for the rest of the data set by dragging the green box down to the end of the table.


Click on Enter to see the final output.


Let Us Understand The Formula

The benefit of understanding the formula is that it becomes simple to figure out how the particular formula functions in Ms Excel. Once you master it, it becomes easier to modify and optimise your data on the spreadsheet.

=IF(C2 > B2 + TIME(8,0,0),TRUE,FALSE)


In the formula, the IF function is used. You can use it to make logical comparisons between values. Based on the example above, Cell B2 shows the time the employees logged in, and Cell C2 shows when they logged out. If the period of time is greater than 8 hours, the output shall be "True"; otherwise ", False".

The formula mentioned in the steps can also be modified to select the cell range of your choice, as well as to modify the time in hours, minutes, and seconds. You can also change the output text. For example, we modify the formula as

=IF(C2 > B2 + TIME(8,0,0),"PASS","FAIL")

After following steps 1 - 3, we click control + shift+ enter and see the result −


Conclusion

Tutorials Point has many such tutorials that explain Excel in a simpler and more detailed manner. Check out how to repeat a sequence of numbers in excel.

raja
Updated on 20-Sep-2022 07:32:42

Advertisements