Need help with VBA for time change when result is different day

[logandiana]

I have column D of times that I need to subtract 5 hours from each.
I wrote the following code:

For i = 2 To LR
    If IsEmpty(data.Cells(i, 4)) = False Then
        data.Cells(i, 4) = DateAdd("h", -5, data.Cells(i, 4)
   End If
Next i

I wasn't sure if this was the best way or not, but it seemed to work just fine until it came to a line where the time was 3 am for example. I get an error.
This made me think of another problem though. If it is 3 am and I am subtracting 5 hours, assuming I can fix the error, for those instances I also need to subtract 1 day from column C, which has the date.
Can someone help here?
Thanks

 

[John_w]

Try this:

    Dim dt As Date
    For i = 2 To LR
        If IsEmpty(data.Cells(i, 4).Value) = False Then
            dt = data.Cells(i, 3).Value + data.Cells(i, 4).Value
            data.Cells(i, 3).Value = Int(DateAdd("h", -5, dt))
            data.Cells(i, 4).Value = DateAdd("h", -5, dt) - Int(DateAdd("h", -5, dt))
        End If
    Next i

 

[logandiana]

A different angle than I was thinking, but it works! Thanks
Came up with this and it seems to work as well.

For i = 2 To LR
    If IsEmpty(data.Cells(i, 3)) = False And data.Cells(i, 4).Value < TimeValue("5:00:00") Then
        data.Cells(i, 3) = DateAdd("d", -1, data.Cells(i, 3))
    End If
Next I
For i = 2 To LR
    If IsEmpty(data.Cells(i, 4)) = False Then
        data.Cells(i, 4) = DateAdd("h", 19, data.Cells(i, 4))
    End If
Next i