Time Comparisons Failing

[Ark68]

I am having difficulty in getting the needed results from my code.

I have a variable dpgo, declared as DATE. This variable represents a time.

dpgo = prgm_start - TimeSerial(1, 0, 0)

In my testing, dpgo = 7:00:00 AM.

dpgo is then compared to the time values in a range of cells (B17:B26) of a database. If this value is less than or equal to dpgo (any times before or at 7:00AM in this example, it is considered valid and the data in the database remain. Any "invalid" data (where the time values in the range are greater than 7:00AM in this example) are eliminated. At the end of the loop, the database is reduced to show only those rows in which the time in column B is equal or less than 7:00AM (in the testing example). But when I run my code, all rows remain despite the value in column B.

Here is the code:

                         For P = 17 To 26
                            If tpgo = "<" Then
                                If ws_sbase.Cells(P, 2) >= CDbl(dpgo) Then 'those that start before groom offset time, when the only program, is eligible
                                    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

When I step through the code, when I hover over ws_sbase.Cells(P, 2) I get a decimal value, I am assuming the time equivalent? When I hover over CDbl(dpgo) I get 7:00:00 AM.

Here's my database that is used in the comparison ...

Excel 2010
	A	B	C	D	E
16	WP
17	CWP	7:00 AM	3:00 PM	2	1
18	WPE	7:00 AM	3:00 PM	A	2
19	CUE	11:00 AM	7:00 PM	4	3
20	WPL	1:30 PM	9:30 PM	C	4
21	CUL	4:00 PM	12:00 AM	5	5
22	CRP	7:00 AM	3:00 PM	3	6
23	HPE	7:00 AM	3:00 PM	A	7
24	RPE	7:00 AM	3:00 PM	A	8
25	HPL	1:30 PM	9:30 PM	C	9
26	RPL	1:30 PM	9:30 PM	C	10
SBASE

Is anyone able to advise what may be wrong and what I can do to overcome this problem?

 

[Ark68]

I'm sorry ... I had already answered my own question.

https://www.mrexcel.com/forum/excel...-comparison-functio-giving-wrong-results.html

 

[LockeGarmin]

I'm afraid in my tests I'm getting the opposite of what you're seeing and it is erasing everything (which I would expect since all values in column B are greater than or equal to dpgo). Admittedly I had to make some tweaks to test on my own machine since I can't see the rest of your code. What I did was put the data you supplied in cells A1:E10, and then ran the following:

Sub Test()

Dim dpgo As Date
Dim P As Long
  
  'Restore Data
  [A17:E26].Value2 = [A1:E10].Value2
  dpgo = TimeValue("7:00 AM")
  
  For P = 17 To 26
    'If tpgo = "<" Then
    If ActiveSheet.Cells(P, 2) >= CDbl(dpgo) Then 'those that start before groom offset time, when the only program, is eligible
      ActiveSheet.Range("B" & P & ":C" & P).ClearContents
      ActiveSheet.Range("D" & P) = "X"
      ActiveSheet.Range("E" & P) = ""
    End If
    'End If
  Next P
  
End Sub

Also for me hovering over CDbl(dpgo) produces "0.291666666667". I'm rather surprised at what you say you're seeing when you hover over the same value.