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:

`=IF(B5>$E$5, "Yes","No")`

- 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:

`=MROUND(C5-B5, 1/86400) >= "1:00:00" * 1`

- 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:

`=IF(C5 > B5 + TIME(1,0,0),"FAIL","PASS")`

- Drag the
**Fill Handle**down to cell**D10.**

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

**Formula Breakdown**

**➤ TIME(1,0,0)**

- Here the
**TIME**function denotes**1:00:00**. The first argument denotes 1 hour, the second argument denotes 0 minutes and the third argument denotes 0 seconds.

**➤ IF(C5 > B5 + TIME(1,0,0),”FAIL”,”PASS”)**

- The logical argument checks whether the time value in cell
**C5**is greater than the time value in cell**B5**+ the result of the**TIME**function. If the logical argument is true, then the**IF**function will return “**FAIL**”, otherwise it will return “**PASS”**.

### 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:

`=(C5-B5)*86400`

- 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:

`=D5/3600`

- 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:

`=IF(E5>1,"Fail","Pass")`

- 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:

`=VLOOKUP(B5,$E$5:$F$6,2,TRUE)`

- 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:

`=XLOOKUP(B5,$E$5:$E$6,$F$5:$F$6,,-1)`

- 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**