Time comparison functio giving wrong results

[Ark68]

I am having problems getting this code to return the results expected.

    For P = 38 To 47
        If ws_sbase.Range("B" & P) > CDate(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
    Next P

Here is the databasethat this code is working with:

Excel 2010
	A	B	C	D	E
37	RP
38	CRP	7:00 AM	3:00 PM	X	1
39	CWP	7:00 AM	3:00 PM	X	2
40	RPE	7:00 AM	3:00 PM	B	3
41	RPL	1:30 PM	9:30 PM	A	4
42	HPE	7:00 AM	3:00 PM	B	5
43	WPE	7:00 AM	3:00 PM	B	6
44	CUE	7:00 AM	3:00 PM	5	7
45	HPL	1:30 PM	9:30 PM	A	8
46	WPL	1:30 PM	9:30 PM	A	9
47	CUL	4:00 PM	12:00 AM	6	10
SBASE

CDate(dpgo) in my testing = 7:00:00 AM

The code steps through each cell in range B38:B47 comparing the time in that cell with the time value of dpgo. If the time in cell B is greater than dpgo, that crew is excluded ad the cells B:D are cleared. All the others remain.

If this code was working, B41:D41, B45:D47 would be blanked out as the time value in B > dpgo. The others are = dpgo.

But in my testing, this code will blank the entire range.

Is anyone able to suggest why this is giving me the wrong results.

 

[Delfinus25]

Doesn't CDate convert it to a date? Do you have a date component on both elements for proper comparison?

 

[Ark68]

It appears as though the decimal equivalents of ws_sbase.Range("B" & P) - 7:00 PM (.291666) and dpgo - also 7:00 PM (0.291663) are different. ws_sbase.Range("B" & P) is greater than dpgo when you consider their decimal eqivalents. I assume that is the problem.

How can I overcome this?

 

[pvr928]

Hi Delifinus25

Strip out the date formatting on the cells you are comparing and convert to number format with a few decimal places (ie "#,##0.0000") and then make the comparison. You'll see that 6am on 3April17 and 6am on 19Sep16 will both appear "6:00:00 AM" if that is how you have formatted it, but of course, 3April17 is greater than 19Sep16.

6AM 3April17 will appear 42828.25 as a number; 6AM 19Sep16 as 42632.25

The 0.25 is the "6AM" - a quarter of a whole number (6 / 24 hours).

Consequently you will have to make sure that you are using the required numbers 'behind' the times to get the right answer.

Have fun!

Cheers

pvr928

 

[Ark68]

Thank you all for your support.
I tracked down my problem. dpgo was a calculation of a time minus a decimal equivalent of 1 hour. (dpgo = prgm_start - 0.04167). This skewed the answer a bit. I changed the formula to dpgo = prgm_start - TimeSerial(1, 0, 0), and this resulted in the two values being equal. But I still wast able to get the comparison right. Once I rounded the two values to 5 decimal places, it finally worked.

                           For P = 38 To 47
                                ctime = ws_sbase.Range("B" & P)
                                dtime = dpgo
                                If Round(ctime, 5) > Round(dtime, 5) 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
                            Next P

Thanks for pointing me in the right direction.

 

[joeu2004]

Doesn't CDate convert it to a date?

No. It returns a value of type Date. Values of type Date can include time, which is just a fraction of a day; or it can be time only (integer part is zero).

In some contexts, VBA treats type Date values in a special way that causes time to be rounded to the second. For that reason, I eschew the use of type Date, opting to use type Double instead.

CDate(dpgo) has dubious purpose in Ark68's context. We cannot be sure, since we do not know the type of dpgo. Presumably, it is type Variant or undeclared, which defaults to type Variant. In that case, the internal value of CDate(dpgo) is no different from dpgo by itself. Try the following example.

Sub doit()
Dim dbl As Variant
Dim dat As Date
dbl = 123.456789012345
dat = dbl
MsgBox dat & vbNewLine & _
   WorksheetFunction.Text(dbl, "m/d/yyyy h:mm:ss.000") & _
   vbNewLine & (dat > dbl)
End Sub

 

[Ark68]

Hi joeu2004, thanks for the lesson! I appreciate any opportunity to learn.