adding & Subtracting date & time in VBA
 

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

adding & Subtracting date & time in VBA
 

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


--
AndyR
------------------------------------------------------------------------
AndyR's Profile: http://www.excelforum.com/member.php...o&userid=15203
View this thread: http://www.excelforum.com/showthread...hreadid=394457

adding & Subtracting date & time in VBA
 

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"
wrote in message
...

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

adding & Subtracting date & time in VBA
 

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


--
jasonsweeney
------------------------------------------------------------------------
jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222
View this thread: http://www.excelforum.com/showthread...hreadid=394457