adding & Subtracting date & time in VBA

  • Thread starter Thread starter jasonsweeney
  • Start date Start date
J

jasonsweeney

I have a user form that does the following:

(1) A textbox records the time when the userform opens (and the
employee's use of the form begins); and

(2) a different textbox records the time when the employee presses a
commandbutton signifying the conclusion of the data entry.

(3) In a third text box, I am trying to subtract time #2 from time #1
to calculate the total time spent by the employee on the particular
data entry item.

The code for entry of time in either textbox is simply:
___________
sub startime()
starttime = time
Userform1.textbox1.value = starttime
end sub
____________
sub endtime()
endtime = time
userform1.textbox2.value = endtime
' the following code doesn't work
userform1.textbox3.value = userform1.textbox2.value -
Userform1.textbox1.value
end sub
___________

Ideas? Bonus questions:
(A) I also want the textboxes to format the time as military time
00:00:00.
(B) It would be great if the start-time text box actually looked like a
timer and counter the minutes and seconds in real time
 
You might want to adapt the following.... the two times are set in t1 and
t2, the difference (DateDiff) is computed in seconds, and converted to a
time serial by setting the seconds value only. Note: values allowed
are -32,768 to 32,767, which translates to just over 9 hours - if that is a
problem you will need to convert the value in other ways!

Dim a As Date, b As Date
t1 = #4:15:17 PM#
t2 = #3:16:18 PM#
MsgBox TimeSerial(0, 0, DateDiff("s", t2, t1))


--
Cheers
Nigel



"jasonsweeney" <[email protected]>
wrote in message
news:[email protected]...
 
Alright. I solved my own problem. I use a userform named "entry." The
text boxes are named in a way that makes sense (see below). On the
user form I have three text boxes and two command buttons. When you
press a button named Button_starttime, the text box named
textbox_markstart gets populated with the current time (format is
military time). When the user presses the button named
Button_stoptime, the textbox named textbox_markend populates with the
time again, and a third text box, named textbox_TIME displays the
amoount of time transpired. Here is the code:
___________________________________
Private Sub Button_Starttime_Click()
Dim startime As String
Dim insertstatime As String
startime = TimeValue(Time)
insertstatime = Format(startime, "Hh:mm:ss")
entry.TextBox_markstart.Value = insertstatime
End Sub
___________________________________
Private Sub Button_stoptime_Click()
Dim starttime As String
Dim stoptime As String
Dim insert_stoptime As String
Dim mytime
Dim posttime
Dim hour_stop
Dim minute_stop
Dim second_stop
' Start Time
starttime = entry.TextBox_markstart.Value
hour_start = Hour(starttime)
minute_start = Minute(starttime)
second_start = Second(starttime)
' Stop Time
stoptime = Time
hour_stop = Hour(stoptime)
minute_stop = Minute(stoptime)
second_stop = Second(stoptime)
insert_stoptime = Format(stoptime, "hh:mm:ss")
entry.TextBox_markend.Value = insert_stoptime
' Difference Between Start and Stop Time
mytime = TimeSerial(hour_stop, minute_stop, second_stop) -
TimeSerial(hour_start, minute_start, second_start)
posttime = Format(mytime, "hh:mm:ss")
' Post Time
entry.TextBox_TIME.Value = posttime


End Sub
 
Back
Top