Return Expected Value If Time Is Greater Than 1 Hour in Excel

In this article, we discuss several ways to determine if a difference between two times is greater than 1 hour in Excel. We’ll use a simple dataset of some start and submission times for an exam, and calculate whether the period in between is greater than an hour or not.

Method 1 – Using the IF Function

In this example, we have a cut-off time limit, along with the actual submission times of some papers. In the Remarks column, we’ll calculate whether each submission time is before or after the time limit using the IF function, and store a Yes or No response for each corresponding cell in the Time column.

Steps

• Select cell C5 and enter the following formula:

• Drag the Fill Handle down to cell C10.

The result (Yes, the submission time was before the 1 hour cut-off, or No, it wasn’t) for each Time value is returned.

Method 2 – Using the MROUND Function

The MROUND function returns the rounded value of the first argument to the nearest multiple of the second argument. Let’s use it to determine whether the difference between the Start Times and End Times are greater than 1 hour or not.

Steps

• Select cell C5 and enter the following formula:

• Drag the Fill Handle down to cell D10.

The results of whether the time difference is greater than 1 hour or not are returned.

Method 3 – Combining the IF and TIME Functions

We can combine the IF and TIME functions to create a formula that will return remarks on whether the time difference is greater than 1 hour or not. The formula will return FAIL if the difference between Start Time and Submission Time is greater than 1 hour and PASS if it isn’t.

Steps

• Select cell D5 and enter the following formula:

• Drag the Fill Handle down to cell D10.

The results of whether the time difference is greater than 1 hour or not are returned.

Method 4 – Calculating the Difference in Hours

In this method, we will calculate the differences in time directly in seconds units, convert these values to hour units, then determine whether the time difference is greater than 1 hour or not.

Steps

• Select cell D5 and enter the following formula:

• Drag the Fill Handle to cell D10.

The range of cell D5:D10 is filled with the difference in time between submission time and start time in seconds.

• In cell E5, enter the following formula:

• Drag the Fill Handle to cell E10.

The range E5:E10 is filled with the difference in time between submission time and start time in hours.

• Select cell F5 and enter the following formula:

• Drag the Fill Handle down to cell F10.

The range of cell F5:F10 is filled with the results of whether the time difference between the times is greater than 1 hour or not. If the difference is greater than 1 hour then the remark will be “Pass”, else it will be “Fail”.

Method 5 – Using the VLOOKUP Function

We can use the VLOOKUP function to look for a certain value in a column and return the corresponding value from another column.

Steps

• Select cell C5 and enter the following formula:

• Drag the Fill Handle down to cell C10.

The range C5:C10 is filled with either On time or Late, depending on whether the time difference is greater than 1 hour or not.

Method 6 – Using the XLOOKUP Function

The XLOOKUP function looks for a certain value in an array and returns the corresponding values from a lookup array.

Steps

• Select cell C5 and enter the following formula:

• Drag the Fill Handle down to cell C10.

The range C5:C10 is filled with either On time or Late, depending on whether the time difference is greater than 1 hour or not.

Method 7 – Embedding VBA Code

in our final method, we’ll create a VBA macro to determine the times which are greater than 1 hour quickly and efficiently.

Steps

• If you don’t see it on your ribbon, enable the Developer tab.
• Go to the Developer tab and click on Visual Basic. Or press ‘Alt+F11’.

• In the dialog box that opens, click on Insert > Module.
• In the Module editor window that opens, enter the following code:

Sub Excel_If_Time_is_Greater_Than_and_Less_Than()
Dim rng As Range
Set rng = Application.InputBox( _
      Title:="Exceldemy", _
      Prompt:="Select the range of cell", _
      Type:=8)
On Error GoTo 0
For Each cel In rng
If cel.Value > Range("E5").Value And cel.Value < Range("E6").Value Then
cel.Offset(0, 1) = Range("F5").Value
Else
cel.Offset(0, 1) = Range("F6").Value
End If
Next
End Sub

• Save the code.
• Close the Module window.
• Go to View > Macros > View Macros.

• In the dialog box that opens, select the macro just created: Excel_If_Time_is_Greater_Than_and_Less_Than.
• Click Run.

A range box opens, asking for the range of cells to be evaluated.

• Select B5:B10 and click OK.

The range C5:C10 is filled with a remark as to whether the time is greater than 1 hour or not.

Download Practice Workbook

<< Go Back to If Time Between Range | Formula List | Learn Excel