adding & Subtracting date & time in VBA

[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

[AndyR]

I find this page great for reminding me how to use dates and times (formats and calculations) in vb.

http://www.georgehernandez.com/xComp...eFunctions.htm

[Nigel]

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]...
>
> 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
>
>
> --
> jasonsweeney
> ------------------------------------------------------------------------
> jasonsweeney's Profile:
http://www.excelforum.com/member.php...fo&userid=5222
> View this thread: http://www.excelforum.com/showthread...hreadid=394457
>

[jasonsweeney]

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