Problem With VBA Formula To Determine Difference Between Two Times

[Ark68]

I am trying to populate a userform textbox with the difference between two times found in two other textboxes of the same userform.

Textboxes cu2_start = "7:00 AM" and cu2_end = "15:00". I am looking to populate cu2_hours with "8.00".

I have this line of code

.cu2_hours.Value = format(DateDiff("h", .cu2_end.Value, .cu2_start.Value) / 60, "0.00")

It's giving me an answer of -.13. Clearly there is something wrong with my formula.

 

[yky]

See if the following works:

.cu2_hours.Value = format(DateDiff("h", .cu2_start.Value, .cu2_end.Value) / 24, "short time")

 

[Ark68]

Hi yky! Thank you ...
I am getting correct value now, but the format isn't right. It's giving me the value as a time (8:00) rather than a decimal number (8.00).
What kind of workaround can I use instead of the "short time"?

 

[yky]

Oh! Sorry! Did not realize you were looking for "8.00" instead of "8:00".

What is "8.00"? Is that eight hours? If it is eight hours and forty minutes, what do you expect to see?

 

[yky]

--

 

[Ark68]

Sorry for the tardy reply ...

Looking for 8.00 = 8 hours, 8.25 = 8 hours, 15 minutes, etc

 

[yky]

Try this:

.cu2_hours.Value = format(DateDiff("n", .cu2_start.Value, .cu2_end.Value) / 60, "general number")

 

[Ark68]

Thank you yky! I substituted your suggestion... and for the most part getting the results I expect.
If you don't mind continuing your support, or anyone else for that matter is encouraged to chime in, I ran into a problematic situation with my code.

1) How does this code change with the end time (.cu2_end) is midnight (the start of the next day) and the start time (.cu2_start) is 4:00PM? The code as it is now returns a value of -16 in this example. The answer expected is 8.0 hrs. difference.

 

[yky]

I have answered the exact question some time ago, maybe more than one year ago. I can't remember what I said. The center of the question is midnight. Midnight can be either the beginning of a day or the end of a day. The former has a time value of 0, the later 1.

Try this:

.cu2_end.Value = IIF(.cu2_end.Value=0, 1,0)

.cu2_hours.Value = format(DateDiff("n", .cu2_start.Value, .cu2_end.Value) / 60, "general number")

 

[Ark68]

yky, once again thank. I really do appreciate you sticking it out to help me with this. It is very kind.

With your suggestion, I was having some odd results.

with .cu2_start = "16:00" (4:00pm) and .cu2_end = "0:00", with the simple addition of your suggested line of code I got these results displayed on my userform:

.cu2_end = "0" ... which makes sense I suppose since we changed the value of cu2_end with the IIF statement
.cu2_hours = "-16" (instead of 8.0)

I changed the code slightly ...

        Dim jt as integer
        .cu2_start.Value = format(Application.WorksheetFunction.VLookup(CLng(.cu2_en.Caption), rg_staff, 5, False), "hh:mm")
        If IsError(.cu2_start.Value) Then
            MsgBox "no match"
            On Error GoTo 0
        Else
            .cu2_end.Value = format(Application.WorksheetFunction.VLookup(CLng(.cu2_en.Caption), rg_staff, 6, False), "h:mm")
            jt = IIf(.cu2_end.Value = 0, 1, 0)
            .cu2_hours.Value = format(DateDiff("n", .cu2_start.Value, jt) / 60, "general number")
        End If

This change returned the display of "0:00" to cu2.end in the userform, but .cu2_hours remained "-16"

In another similar example on the same userform ....
with .cu4_start = "7:00" (7:00am) and .cu4_end = "15:00", and with your code suggestion alone,

.cu4_end = "0" ... and .cu4.hours = "-7".

So, yeah ... something is amok!