Time Comparison Failing in If Stement

[Ark68]

I have this range of data...

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

This code

                      For P = 17 To 26
                            If tpgo = "<" Then
                                ctime = ws_sbase.Cells(P, 2)   'end of shift (time)
                                dtime = dpgo                         'service time
                                If Round(dtime, 5) >= Round(ctime, 5) Then 'eliminate all staff whose shift ends before the service time
                                    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

This code compares the end of shift times from column C of the data (ctime) to the service time (dtime). If the service time is later than the end of shift time , then clear cells B , C, E and put an X in D.

If I coded my logic correctly (and clearly I haven't because I'm not getting these preferred results) all rows with the exception of rows 19, 20, 21 (this one I expected to be problematic due to the end being at midnight), 25 and 26.

But, in my testing, everything was being cleared. Can someone help me figure out the error and avoid the potential midnight problem (I'm afraid it will think its midnight of that day and not the next)?

Any help would be greatly appreciated with this hurdle.

 

[Ark68]

Is there any additional information I can add to help in finding the cause of the problem? This error is causing havoc on my application.

 

[NoSparks]

If dtime is originating from a cell, as is ctime, I'd try using .value2 from the cells
and prior to the >= line
If dtime=0 Then dtime=1.

 

[Ark68]

Hi NoSparks, I really appreciate your effort in helping me find a solution.
The value of dtime doesn't come from a cell, it's from a coded formula ...

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

 

[NoSparks]

Where does prgm_start come from?

Is your coded formula meant to be
dgpo = prgm_start minus 1 AM ?

 

[joeu2004]

ctime = ws_sbase.Cells(P, 2) 'end of shift (time)
[....]
This code compares the end of shift times from column C of the data (ctime)

Then, shouldn't that reference be Cells(P,3) instead?

Even better: Cells(P,"c") to avoid the confusion.

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

Is your coded formula meant to be
dgpo = prgm_start minus 1 AM ?

TimeSerial(1,0,0) can also be interpreted as 1 hour. So the expression is prgm_start minus 1 hour, not literally 1:00 AM.

Nevertheless, you ask the important and next obvious question: what is the value of prgm_start at the time of the failure? And how is its value determined; for example, a calculation in a loop (what is it?), or a cell reference (what is it?)?

@Ark68, learn to anticipate such obvious questions and provide the answers at the outset in order to get a dispositive solution more quickly.

And when showing us the value of pgrm_start or any other time value, be sure to display it as a decimal number with 15 significant digits [1]. The approximate calculated time value might be part of the problem.

[1] 17 significant digits would be even better. But Excel and VBA format only the first 15 significant digits. To work around that, it would be ideal if you display Format(prgm_start,"0.00000000000000E+0") & " + " & Format(prgm_start - Round(prgm_start,15),"0.00E+0"). The first term has 14 zeros after the decimal point.

 

[Ark68]

Thank you joeu2004, the answer, I think was as simple

shouldn't that reference be Cells(P,3) instead

Until I test under a different dataset, I won't know for certain, but so far it seems to be providing better results.

TimeSerial(1,0,0) can also be interpreted as 1 hour. So the expression is prgm_start minus 1 hour, not literally 1:00 AM.

Yes ... I want to subtract one hour from prgm_start.

 

[joeu2004]

Can someone help me figure out the error and avoid the potential midnight problem (I'm afraid it will think its midnight of that day and not the next)?

Yes, it would. Can you enter midnight of the next day as 1, formatted as h:mm AM/PM?

In fact, any time of the next should be entered as =1+TIME(1,30,0) for 1:30 AM next day, for example.

Otherwise, any time of day is ambiguous, unless you can rely on some practical limits; for example, the latest end shift is 5:00 AM the next day, and the earliest start time is 5:01 AM. In that case, you might write:

If Round(dtime - (dtime <= TimeSerial(5,0,1)), 5) >= Round(ctime - (ctime <= TimeSerial(5,0,1)), 5) Then

That adds 1 (day; 24 hours) to ctime and dtime despite the subtraction because True is -1 in VBA, not +1 as it is in Excel.

I add 1 second to TimeSerial(5,0,0) as a poor man's way of accounting for binary floating-point anomalies that might arise if the source of ctime and/or dtime is a calculated time value. I presume that time is intended to be accurate to the minute.

But in general, rounding to a arbitrary number of decimal places is not reliable. IMHO, the following is more reliable, assuming that time is intended to be accurate to the minute:

ctime = CDate(Format(ws_sbase.Cells(P, "c"), "h:mm")): ctime = ctime - (ctime <= TimeSerial(5,0,0))
dtime= CDate(Format(dpgo, "h:mm")): dtime = dtime - (dtime <= TimeSerial(5,0,0))
If dtime >= ctime Then

 

[Ark68]

Thanks again joeu2004. Sometimes I feel I am the only one that struggles with time.

The choice of 5 decimal places came from a lesson learned in this post ...
https://www.mrexcel.com/forum/excel...-comparison-functio-giving-wrong-results.html

Would the concept that member yky introduced to me in this post https://www.mrexcel.com/forum/excel...lure-identifying-time-falls-within-range.html overcome the potential midnight problem?

 

[Ark68]

ctime = CDate(Format(ws_sbase.Cells(P, "c"), "h:mm")): ctime = ctime - (ctime <= TimeSerial(5,0,0))
dtime= CDate(Format(dpgo, "h:mm")): dtime = dtime - (dtime <= TimeSerial(5,0,0))

Why an hour value of 5 in TimeSerial? Was that for illustration purposes or does it relate to what I need to do in my instance?