Formula to Expel Certian Rows of Data Failing . Something wrong with my comparitors

[Ark68]

Please consider this snippit of code:

                            For p = 5 To 14
                                ctime = ws_sbase.Range("B" & p) 'shift start time
                                dtime = dpgo 'preferred groom time (start time - 1 hr)
                                If Round(ctime, 5) >= Round(dtime, 5) Then
                                    If CDate(ws_sbase.Cells(p, 2)) >= CDate(dpgo) Then 
                                        ws_sbase.Range("B" & p & ":C" & p).ClearContents
                                        ws_sbase.Range("D" & p) = "X"
                                        ws_sbase.Range("E" & p) = ""
                                    End If
                                End If
                            Next p

The table below shows the different employee shifts. If an employee's shift is after the preferred service time, that shift is ineligible, and cells B, C and e are blanked out, and an X put in cell D. Those shifts which start at or before the preferred service time, are eligible and no change is made.

SO in my example, the preferred service time is 7:00AM. From the shift chart below, shifts at row 5, 6, 7, 8, 10 and 12 are eligible, and should remain untouched in the dataset. All others are cleared due to ineligibility.

But withmy current code, none are eligible. All the rows are beingblanked out.

Is anyone able to suggest what I need to do to ensure the results I am looking for ...

Excel 2010
	A	B	C	D	E
5	CWP	7:00 AM	3:00 PM	X	1
6	CRP	7:00 AM	3:00 PM	X	2
7	CUE	7:00 AM	2:30 PM	5	3
8	HPE	7:00 AM	2:30 PM	C	4
9	HPL	1:30 PM	9:30 PM	B	5
10	RPE	7:00 AM	2:30 PM	C	6
11	RPL	2:00 PM	10:00 PM	B	7
12	WPE	7:00 AM	2:30 PM	C	8
13	WPL	1:30 PM	9:30 PM	B	9
14	CUL	4:00 PM	12:00 AM	6	10
SBASE

Cell Formulas
Range	Formula
A5	CWP
A6	CRP
A7	CUE
A8	HPE
A9	HPL
A10	RPE
A11	RPL
A12	WPE
A13	WPL
A14	CUL
B5	0.291666666666667
B6	0.291666666666667
B7	0.291666666666667
B8	0.291666666666667
B9	0.5625
B10	0.291666666666667
B11	0.583333333333333
B12	0.291666666666667
B13	0.5625
B14	0.666666666666667
C5	0.625
C6	0.625
C7	0.604166666666667
C8	0.604166666666667
C9	0.895833333333333
C10	0.604166666666667
C11	0.916666666666667
C12	0.604166666666667
C13	0.895833333333333
C14	0
D5	X
D6	X
D7	5
D8	C
D9	B
D10	C
D11	B
D12	C
D13	B
D14	6
E5	1
E6	2
E7	3
E8	4
E9	5
E10	6
E11	7
E12	8
E13	9
E14	10

ctime = employee's shift start time (7:00 AM [0.291666667])
dtime = preferred service time, which is the program start time minus 1 hour. eg program start time = 8:00AM, dtime = 7:00AM (0.291666667)

If the shift time is greater than or equal to the service time ...

 

[Ark68]

If I change the shift start times in the database to 6:30 AM, it all works OK.

 

[Marcelo Branco]

This worked for me

Sub aTest()
    Dim ws_sbase As Worksheet, p As Long
    Dim ctime As Double, dpgo As Double
        
    Set ws_sbase = Sheets("SBASE")
    dpgo = TimeSerial(7, 0, 0) 'preferred start time = 7 AM
    
    For p = 5 To 14
        ctime = ws_sbase.Range("B" & p) 'shift start time
        If Round(ctime, 5) > Round(dpgo, 5) Then
            ws_sbase.Range("B" & p & ":C" & p).ClearContents
            ws_sbase.Range("D" & p) = "X"
            ws_sbase.Range("E" & p) = ""
        End If
    Next p
End Sub

M.

 

[Ark68]

Thank you Marcelo!!!!