Difference between hours and time

[Lovelylou79]

Hi there,

I am looking for some help regarding identifying differences between a recorded amount of hours and the recorded duration (Start/end Time).
In the following table Duration is calculated using "=B2-A2+(A2>L2).
Row 1 would not need highlighting as duration is less than 60 minutes over the hours, Row 2 would need to be highlighted as hours are less than duration, Row 3 hours and duration match so no highlight is needed, and Row 4 hours are higher than the duration.
I would prefer this in vba as it is part of a larger function. Thank you!!
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Hours[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]07:45[/TD]
[TD]11:30[/TD]
[TD]03:45[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12:30[/TD]
[TD]17:30[/TD]
[TD]05:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13:00[/TD]
[TD]16:00[/TD]
[TD]03:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16:00[/TD]
[TD]17:30[/TD]
[TD]01:30[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

try something like this:

Sub Check()

Dim i As Integer
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow                                                    'assumption = table has a headerline.
      If Cells(i, 1).Value < Int(Cells(i, 4).Value * 24) Then           'check value against criteria
        With Range(Cells(i, 1), Cells(i, 4)).Interior                   'highlight cells if nesc.
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
Next i

End Sub

 

[Lovelylou79]

Hi Jorismoering,

Thank you for taking the time to post, greatly appreciated!
I've tried running this code and all cells remain with no fill. I tried changing the < to > but this just highlighted every row.
Any other suggestions?

 

[Lovelylou79]

Hello Again,

I have managed to work this out myself. I seemed to be having trouble due to my values being in different formats (I think). The Hours were Numbers stored as Text and the duration stored as Time. I've fixed this, rather crudely, by reformatting them both to text with the following;

Sub test()
'Test all the data manipulations for XXXX Report

Dim lastrow As Integer
lastrow = FindLastRow

'Calculate/Show duration from Start Time to End time.
Range("D2:D" & lastrow).Formula = "=RC[-1]-RC[-2]+(RC[-2]>RC[-1])"

'Covert Hours from Text to Value
Range("F2:F" & lastrow).Formula = "=VALUE(RC[-5])"
Range("F2:F" & lastrow).Copy
Range("A2:A" & lastrow).PasteSpecial Paste:=xlPasteValues
Range("F2:F" & lastrow).Delete shift:=xlToLeft

'Convert Duration to Value
Range("F2:F" & lastrow).Formula = "=TEXT(RC[-2],""h"")"
Range("G2:G" & lastrow).Formula = "=VALUE(RC[-1])"
Range("G2:G" & lastrow).Select
Selection.Copy
Range("D2:D" & lastrow).PasteSpecial Paste:=xlPasteValues
Range("D2:D" & lastrow).NumberFormat = "General"
Range("F2:G2" & lastrow).Delete shift:=xlToLeft

End Sub

I then ran a modified version of Jorismoering's code above:

Sub Check()

Dim i As Integer
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow                                                    'assumption = table has a headerline.
      If Cells(i, 1).Value <> Int(Cells(i, 4).Value) Then           'check value against criteria
        With Range(Cells(i, 1), Cells(i, 4)).Interior                   'highlight cells if nesc.
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
       
    End If
Next i

End Sub

I'm certain there is an easier, less novice way of doing this, but it seems to work the way I need it to.
Feel free to simplify if anyone has the need or desire.