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]

You need to set jt to double instead of integer and use the following code:

jt = IIf(.cu2_end.Value = 0, 1, .cu2_end.Value)

 

[Ark68]

OK ... that resolved one of the concerns, but I'm still getting incorrect results for the hours. They are all negative ...

        .cu2_start.Value = format(Application.WorksheetFunction.VLookup(CLng(.cu2_en.Caption), rg_staff, 5, False), "hh:mm")
        .cu2_startbu.Value = .cu2_start.Value
        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")
            .cu2_endbu.Value = .cu2_end.Value
            jt = IIf(.cu2_end.Value = 0, 1, .cu2_end.Value)
            .cu2_hours.Value = format(DateDiff("n", .cu2_start.Value, jt) / 60, "general number")
        End If

When I watch this code, the value for "jt" always ends up equaling 0 (ie when .cu2_end.value = "16:00" or any other valid time). It appears as if the .cu2_hours is simply the negative of .cu2_start.

 

[yky]

You cannot set jt to integer. Otherwise, fractions will be rounded to either 1 or 0.

 

[Ark68]

I had set jt to double on your advice from post 11 (I failed to include it in my posted code though). jt wasn't defined as an integer any longer when I posted #12 , so the problem still exists.

 

[yky]

You mean after this line "jt = IIf(.cu2_end.Value = 0, 1, .cu2_end.Value)" is executed, the value of jt is zero? That's odd.

I just tried this code with the value of A1 being 16:00 and got a jt value of 0.6666666667.

Sub test()
Dim jt As Double
jt = IIf(Range("A1").Value = 0, 1, Range("A1").Value)
End Sub

When jt was defined as integer, I got a jt value of 1.

I don't know why you always got zero. Can't explain it.

 

[Ark68]

Oh no! Unable to explain it? I sure as heck can't if you can't LOL.
Yes ... with "jt" defined as double, I am always getting "jt" = 0 (when .cu2_end = "15:00") which results in a negative value (-7 ... .cu2_start = "7:00") in the hours textbox.

I'm not sure how that IIF statement works, but if .cu2_end requires a date component, it won't. Should the text value be converted to a true date and time first as its just text? Just throwing out by naivety

In Excel you can usually get results from more than one method. Any suggestions on an possible alternative to getting the difference between two times, including the difference between two times if the end time carries over into the next day (midnight and beyond? ie start 16:00 to 0:00 = 8.0 hrs))

 

[yky]

In my example above, the format of A1 is "hh:mm". It is a format of time, not text. When I copied it and pasted it to another cell by value, I got 0.66666667. If I formatted it as text, then, enter 16:00, the same code gave me an error message of "type mismatch".

Looks like you need to convert the text value to date. I wonder why you did not get an error message like I did.

 

[Ark68]

I will try that. The "time" values I am using come from textboxes in a userform, so they are text that just look like time. So yeah, the key may be in having to change to an actual true time format.
Why am I not getting the error ... is because I am using some (inappropriate?) error handling to catch missing .cu2_start values. You can see part of that error handing in snippets I've been sharing. An expansion of this code would reveal ...

    With uf9_poststaff
        On Error Resume Next
        
        .cu2_start.Value = format(Application.WorksheetFunction.VLookup(CLng(.cu2_en.Caption), rg_staff, 5, False), "hh:mm")
        .cu2_startbu.Value = .cu2_start.Value
        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")
            .cu2_endbu.Value = .cu2_end.Value
            jt = IIf(.cu2_end.Value = 0, 1, .cu2_end.Value)
            .cu2_hours.Value = format(DateDiff("n", .cu2_start.Value, jt) / 60, "general number")
        End If

So, if I change both times (.cu2_start and .cu2_end) from text to true times, will our forumula to determine the time difference change?

 

[Ark68]

Here is my latest attempt, with results based on .cu2_start = "4:00 PM" and .cu2_end = "00:00". The required result for .cu2_hours = "8.0" (8 hours difference between 4:00P and midnight the following day day.

Here is my code with running commentary (comments) with results based on .cu2_start = "4:00 PM" and .cu2_end = "00:00". The required result for .cu2_hours = "8.0" (8 hours difference between 4:00P and midnight the following day day.

    Dim stv2, etv2 as double

    With uf9_poststaff
        On Error Resume Next
        
        .cu2_start.Value = format(Application.WorksheetFunction.VLookup(CLng(.cu2_en.Caption), rg_staff, 5, False), "hh:mm")  ' .cu2_start.value = "16:00"
        .cu2_startbu.Value = .cu2_start.Value ' provides a backup to the original cu2_start value
        stv2 = CDate(.cu2_start.Value) ' stv2 = 4:00:00 PM
            MsgBox "no match"
            On Error GoTo 0
        Else ' a lookup match is found for .cu2_en.value"
            .cu2_end.Value = format(Application.WorksheetFunction.VLookup(CLng(.cu2_en.Caption), rg_staff, 6, False), "h:mm") ' .cu2_end.value = "00:00"
            .cu2_endbu.Value = .cu2_end.Value ' provides a backup to the original cu2_end value
            etv2 = CDate(.cu2_end.Value) ' etv2 = 0
            jt = IIf(etv2 = 0, 1, cu2etv) ' jt = 1 
            .cu2_hours.Value = format(DateDiff("n", stv2, jt) / 60, "general number") ' .cu2_hours = "8"
        End If

Success!!(?) I'm getting the correct results so far.
But, I'm still unable to get the proper format. I'm getting "8", but ideally looking for "8.00" (the fractional portion will only ever be 0, .25,.50, and .75)

 

[yky]

Did you set the format of the cell? To get "8.00" you need to set the format of the cell so it'll show 2 decimal.

jt = IIf(etv2 = 0, 1, cu2etv)

Can't see where cu2etv come from>