Start time(pm) and end time( am)calculation

[Ranjeet yamgekar]

Dear sir/ madam

I need your help regarding below

I am a new in vbb

I have made one report
There need total hour work
Example

Textbox 1. 11:15 pm
Textbox 2. 6:15 am

Textbox 3. Require results on text box 3

I have so try

Please send me code or solve this

Please

 

[mole999]

plenty of threads on here , this is just one > Determine time inbetween 2 times

 

[joeu2004]

@Ranjeet yamgekar, it would be prudent to show an example of the VBA calculations that you have.

Basically, assuming that the times are within 24 hours, we add 1 (24 hours) if the time difference is negative.

Ostensibly, that is demonstrated by the following code.

Sub doit1()
Dim tval1 As String, tval2 As String
Dim tval3 As Double, tval4 As Double
tval1 = "11:15 pm"
tval2 = "6:15 am"
tval3 = TimeValue(tval2) - TimeValue(tval1)
If tval3 < 0 Then tval3 = tval3 + 1
tval4 = TimeValue(Format(tval3, "h:m:s"))
MsgBox TimeValue(tval1) & vbNewLine & TimeValue(tval2) & _
    vbNewLine & Format(tval3, "h:mm:ss") & _
    vbNewLine & "exact?  " & (tval3 = tval4)
End Sub

But note that "exact?" is False (!).

The reason is: often, time calculations do not have the exact binary result as the equivalent time constant (tval4).

The infinitesimal binary difference might be acceptable if you simply want to display the elapsed time.

However, the infinitesimal difference can cause some comparisons and lookups to fail or return unexpected results. And it can cause incorrect results of subsequent dependent calculations.

As demonstrated with tval4, it is prudent to round the time calculation to the desired precision; e.g. to the second. Caveat: the Format function only rounds seconds; it truncates hours and minutes.

The following code puts the two together.

Sub doit2()
Dim tval1 As String, tval2 As String
Dim tval3 As Double, tval4 As Double
tval1 = "11:15 pm"
tval2 = "6:15 am"
tval3 = TimeValue(tval2) - TimeValue(tval1)
tval3 = TimeValue(Format(tval3 + IIf(tval3 < 0, 1, 0), "h:m:s"))
tval4 = TimeValue(Format(tval3, "h:m:s"))
MsgBox TimeValue(tval1) & vbNewLine & TimeValue(tval2) & _
    vbNewLine & Format(tval3, "h:mm:ss") & _
    vbNewLine & "exact?  " & (tval3 = tval4)
End Sub

Now, "exact?" is True.

Aside.... Note that I use type Double, not type Date. The reason is: VBA does "funny things" with values that are type Date sometimes. When I want to display tval3 as date and/or time, I use CDate(tval3).

Also, if the times are not within 24 hours, you should include the date or the difference in days as (the integer) part of the "time". In that case, nothing special needs to be done. The straight-forward subtraction should have the intended result.

 

[Ranjeet yamgekar]

not solve sir

please need your help

can you send in excel

 

[Ranjeet yamgekar]

@Ranjeet yamgekar, it would be prudent to show an example of the VBA calculations that you have.

Basically, assuming that the times are within 24 hours, we add 1 (24 hours) if the time difference is negative.

Ostensibly, that is demonstrated by the following code.

Sub doit1()
Dim tval1 As String, tval2 As String
Dim tval3 As Double, tval4 As Double
tval1 = "11:15 pm"
tval2 = "6:15 am"
tval3 = TimeValue(tval2) - TimeValue(tval1)
If tval3 < 0 Then tval3 = tval3 + 1
tval4 = TimeValue(Format(tval3, "h:m:s"))
MsgBox TimeValue(tval1) & vbNewLine & TimeValue(tval2) & _
    vbNewLine & Format(tval3, "h:mm:ss") & _
    vbNewLine & "exact?  " & (tval3 = tval4)
End Sub

But note that "exact?" is False (!).

The reason is: often, time calculations do not have the exact binary result as the equivalent time constant (tval4).

The infinitesimal binary difference might be acceptable if you simply want to display the elapsed time.

However, the infinitesimal difference can cause some comparisons and lookups to fail or return unexpected results. And it can cause incorrect results of subsequent dependent calculations.

As demonstrated with tval4, it is prudent to round the time calculation to the desired precision; e.g. to the second. Caveat: the Format function only rounds seconds; it truncates hours and minutes.

The following code puts the two together.

Sub doit2()
Dim tval1 As String, tval2 As String
Dim tval3 As Double, tval4 As Double
tval1 = "11:15 pm"
tval2 = "6:15 am"
tval3 = TimeValue(tval2) - TimeValue(tval1)
tval3 = TimeValue(Format(tval3 + IIf(tval3 < 0, 1, 0), "h:m:s"))
tval4 = TimeValue(Format(tval3, "h:m:s"))
MsgBox TimeValue(tval1) & vbNewLine & TimeValue(tval2) & _
    vbNewLine & Format(tval3, "h:mm:ss") & _
    vbNewLine & "exact?  " & (tval3 = tval4)
End Sub

Now, "exact?" is True.

Aside.... Note that I use type Double, not type Date. The reason is: VBA does "funny things" with values that are type Date sometimes. When I want to display tval3 as date and/or time, I use CDate(tval3).

Also, if the times are not within 24 hours, you should include the date or the difference in days as (the integer) part of the "time". In that case, nothing special needs to be done. The straight-forward subtraction should have the intended result.

THANK YOU SO MUCH SIR

 

[joeu2004]

not solve sir [....] can you send in excel

THANK YOU SO MUCH SIR

You're welcome. If you still need me to provide a complete Excel/VBA implementation, it would behoove you to provide yours first, so that I can amend it.

Since this is VBA code, an XL2BB snippet is not sufficient. You could provide __all__ of your VBA code, including the userforms (textboxes). But I think it would be easier for both of us if you upload an example Excel file (redacted) to a file-sharing website, and post the download URL in a response here.

 

[joeu2004]

tval3 = TimeValue(Format(tval3 + IIf(tval3 < 0, 1, 0), "h:m:s"))

Probably better:

tval3 = TimeValue(Format(IIf(tval3 < 0, tval3+1, tval3), "h:m:s"))