When a time goes into the next day, negative hour error

[Corey ....]

I am trying to put together a TimeSheet, but have ran into a problem.
If th finish time is less than the start time(IE. ends the next working day)
then i get a negative total hours result.
Why does not this line:
((TimeValue(TextBox9.Value) - TimeValue(TextBox8.Value)) +
(TimeValue(TextBox9.Value) > TimeValue(TextBox8.Value))) * 24

Correct this negative result?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If (TimeValue(TextBox9.Value) > TimeValue(TextBox8.Value)) Then
Label63.Caption = (TimeValue(TextBox9.Value) - TimeValue(TextBox8.Value)) *
24
If (TimeValue(TextBox9.Value) <= TimeValue(TextBox8.Value)) Then
Label63.Caption = ((TimeValue(TextBox9.Value) - TimeValue(TextBox8.Value)) +
(TimeValue(TextBox9.Value) > TimeValue(TextBox8.Value))) * 24
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Corey....

 

[OssieMac]

Hi Corey,

The following demo adds a day to the time which has gone into next day if
the finish time is less than the start time. However, if the time difference
is greater than 23:59 then it is not going to work. You should realize that
ino VBA times and dates are all handled on date variables.

I have done this on a worksheet but it should work just as well on a Userform.

Sub TimeDifferences()

Dim timeDiff As Date
Dim timeOneDay As Date

timeOneDay = #1/1/1900#

With ActiveSheet
.TextBox9.Value = "4:30" 'Finish time tomorrow morning
.TextBox8.Value = "21:30" 'Start time today

If TimeValue(.TextBox9.Value) <= TimeValue(.TextBox8.Value) Then
'Add one day to the finish time
timeDiff = (TimeValue(.TextBox9.Value) + timeOneDay) _
- TimeValue(.TextBox8.Value)
Else

timeDiff = TimeValue(.TextBox9.Value) _
- TimeValue(.TextBox8.Value)

End If

.Label63 = Format(timeDiff, "hh:mm")

End With

End Sub

 

[OssieMac]

I meant to add that you don't have to create a variable for the date which is
the first day of dates in Excel. I only used that for demonstration purposes.
You can simply use 1 as per the following example.

timeDiff = (TimeValue(.TextBox9.Value) + 1) _
- TimeValue(.TextBox8.Value)