Comparing times with different text formats

[Ronanm]

Hi

I am trying to compare two lots of times and got as far as the code below. In Cells A1, A2 and A3 I have three times in the format +02'00... and in Cells B1, B2, B3 I have three times in the format 00:17:00. If the time in Column A are greater than the times in Column B I want a Yes in Columns C. but I keep seeing the times as 1.18055 for example.

Sub testing()
Dim cell As Range
    For Each cell In Range(Cells(1, 1), Cells(3, 1))
        If cell.Value <> "" Then
            If Mid(cell.Offset(0, 0).Value, 2, 2) * 60 + Mid(cell.Offset(0, 0).Value, 5, 2) >= Format(Mid(cell.Offset(0, 1).Value, 4, 2) * 60 _
                + Mid(cell.Offset(0, 1).Value, 7, 2), "hh:mm:ss") Then
                cell.Offset(0, 3).Value = "Yes"
                Else
                cell.Offset(0, 3).Value = "No"
            End If
        End If
    Next cell
End Sub

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
[TD="width: 64"]+02'00
[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64"]00:17:00
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]+03'00
[/TD]
[TD]00:01:00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]+00'30
[/TD]
[TD]00:02:00
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]







Thanks

 

[Fluff]

How about

Sub testing()
Dim cell As Range
    For Each cell In Range(Cells(1, 1), Cells(3, 1))
        If cell.Value <> "" Then
            If Mid(cell.Offset(0, 0).Value, 2, 2) * 1 + (Mid(cell.Offset(0, 0).Value, 5, 2) / 60) >= _
               Minute(cell.Offset(0, 1).Value) + (Second(cell.Offset(, 1)) / 60) Then
                cell.Offset(0, 3).Value = "Yes"
                Else
                cell.Offset(0, 3).Value = "No"
            End If
        End If
    Next cell
End Sub

 

[Ronanm]

That works a treat thanks

One little bit extra. I can take one from the other and get the results too as well now thanks. But if I wanted the result in the format of 00:15:00 instead of 15?

Thanks

 

[Fluff]

What do you mean by "the result", it's only putting either Yes or No into the sheet.

 

[Ronanm]

I just thought of putting the resulting time difference on Column D. Which works well using your formulas.

cell.Offset(0, 4).Value = Mid(cell.Offset(0, 0).Value, 2, 2) * 1 + (Mid(cell.Offset(0, 0).Value, 5, 2) / 60) - Minute(cell.Offset(0, 1).Value) + (Second(cell.Offset(, 1)) / 60)

But instead of the result saying 15 I wondered how it could be displayed in the format hh:mm:ss i.e. 00:15:00.
More of a side question really.

 

[Fluff]

Try

Sub testing()
   Dim x, y
   Dim cell As Range
   For Each cell In Range(Cells(1, 1), Cells(3, 1))
      If cell.Value <> "" Then
         x = (Mid(cell.Offset(0, 0).Value, 2, 2) / 60) + (Mid(cell.Offset(0, 0).Value, 5, 2) / 3600)
         y = cell.Offset(, 1).Value * 24
         If x >= y Then
            cell.Offset(0, 3).Value = "Yes"
         Else
            cell.Offset(0, 3).Value = "No"
         End If
         cell.Offset(, 4).Value = Format((x - y) / 24, "hh:mm:ss")
      End If
   Next cell
End Sub

 

[Ronanm]

That works a treat mate. Thank you for your time and assistance.

Appreciate it.

 

[Fluff]

Glad to help & thanks for the feedback