Need help VBA to figure time from a date/time field ??

[logandiana]

I am trying to change font color if the date is less than yesterday AND the time is before 16:00.


Hi the fields are in this custom format. m/d/yyyy h:mm

Here's my code so far and it works for the date part, but I can't figure out how to do the cut off time portion.

LR2 = ELK.Cells(Rows.Count, 1).End(xlUp).Row
xDay = Format(Date, "dddd")
    If xDay = "Monday" Then
        xDate = Date - 3
    Else
        xDate = Date - 1
    End If
  For j = 2 To LR2
    If Cells(j, 14).Value < xDate And Cells(j, 14).Value <> "" Then
        Cells(j, 14).Offset(0, 1).Font.Color = 255
        Cells(j, 14).Offset(0, 2).Font.Color = 255
        End If
    Next j

 

[VBA Geek]

you can shorten your code like this:

For j = 2 To ELK.Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(j, 14).Value < Application.WorkDay(Date, -1) Then
        If Cells(j, 14).Value - Int(Cells(j, 14).Value) < TimeSerial(16, 0, 0) Then
            If Cells(j, 14).Value <> "" Then
                Cells(j, 14).Offset(0, 1).Font.Color = 255
                Cells(j, 14).Offset(0, 2).Font.Color = 255
            End If
        End If
    End If
Next j