Textbox for accepting TIME

[WLMPilot]

I am using a userform to enter Time In/Out via textbox1 (in) & textbox2
(out). Time will be entered based on 24-hr clock, ie 10:00pm is 2200 or
22:00.

Question 1:
Is there a way to format the textbox to include the colon?

Question 2:
I will calculate the difference to determine total hours (with two decimal
places). Currently, this is how I have it (partial macro), but I am getting
zero as answer.
How is the best way to solve this?

DIM ttltime2 As DOUBLE
ttltime2 = VAL(Textbox2) - VAL(Textbox1)

Thanks and Happy New Year!!
Les

 

[Bob Phillips]

Q1. You could format the textbox in its AfterUpdate event

Private Sub TextBox1_AfterUpdate()
With Me.TextBox1
.Text = Format(.Text, "hh:mm AM/PM")
End With
End Sub

Q2. I am getting a value. What is in Textbox1 and 2, and what do you do with
them?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

You have other responses at your other posts.

I am using a userform to enter Time In/Out via textbox1 (in) & textbox2
(out). Time will be entered based on 24-hr clock, ie 10:00pm is 2200 or
22:00.

Question 1:
Is there a way to format the textbox to include the colon?

Question 2:
I will calculate the difference to determine total hours (with two decimal
places). Currently, this is how I have it (partial macro), but I am getting
zero as answer.
How is the best way to solve this?

DIM ttltime2 As DOUBLE
ttltime2 = VAL(Textbox2) - VAL(Textbox1)

Thanks and Happy New Year!!
Les

 

[Mike Fogleman]

You need to read your previous posts. Most of your answers are there.

Mike F

 

[WLMPilot]

Textbox1 & 2 will equal a time (based on 24-hr clock).
Textbox1 (Time Clocked In)
Textbox2 (Time Clocked Out)
ttltime1 = Textbox2 - Textbox1
ttltime2 = HOUR(ttltime1) +(MINUTE(ttltime1)/60)

This is what is suppose to take place
User enters time in/out: In = 08:00 (8am) Out = 17:23 (5:23pm)

Textbox1 = 08:00 Textbox2 = 17:23

ttltime1 = Textbox2 - Textbox1 Answer should be 9:23

ttltime2 = HOUR(ttltime1) + (MINUTE(ttltime)/60 Answer should be 9.38
(hrs)
9 23/60 or .38
ttltime2 = 9.38 (hrs worked)

Also, any help with DIM the variables greatly appreciated.

Thanks,
Les

 

[Rick Rothstein \(MVP - VB\)]

Give this a try....

Dim ttltime1 As String
Dim ttltime2 As Double
ttltime2 = 24 * (CDate(TextBox2.Value) - CDate(TextBox1.Value))
ttltime1 = Format$(ttltime2 / 24, "h:nn")

When (if?) you display ttltime2, you can use Format$(ttltime2, "0.00") in
order to display it to 2 decimal places, but do not round the value before
using it in the calculation for ttltime1 or you might round too much away
and affect the calculation for ttltime1.

Rick

 

[WLMPilot]

I will give it a shot later today and let you know. I believe ttltime1 and
ttltime2 are switched with your calculations. ttltime2 will hold the final
answer (in hours with up to 2 decimal places) and be inserted in a
spreadsheet.

Thanks,
Les

 

[Rick Rothstein \(MVP - VB\)]

No, they are not switched. When you put ttltime2 into your spreadsheet, use
the full value as calculated and then use a (custom?) number format for the
cell (column?) that shows only 2 decimal places.

Rick

 

[Rick Rothstein \(MVP - VB\)]

It just occurred to me... you might not be displaying ttltime1 and only
showed it to us because you thought it was needed in order to calculate
ttltime2. If that is the case, then what I posted for ttltime2 is all you
need... the h:mm format for the time is not required in order to calculate
the hours in decimal format.

Rick

 

[WLMPilot]

Rick, I have not had a chance to try your formula, but I do have a question.
How does CDATE work with time?

Les

 

[Rick Rothstein \(MVP - VB\)]

In VB/VBA, the date is stored as a Double... the whole number part contains
the number of days from 'date-zero' (December 30, 1899 for VB/VBA, which is
one day less than for the spreadsheet date formulas) and the decimal part is
the fraction of the day that has passed. CDate handles the conversion of a
text String to a Date number. If there is only a time value in the String,
then CDate converts it to the fractional part of a 24-hour day and leaves
the number of days from 'date-zero' as 0. So, if your TextBox contains, say,
11:30, then CDate does the following mathematical conversion on it..

(11 + 30 / 60) / 24

to produce the Double Date value of 0.479166666666667 which you can see by
printing out CDbl(CDate("11:30")) in the immediate window. Now, if you just
printed out CDate("11:30"), your system would show you 11:30:00 AM, but that
is because VB hides the Double value for Date values and shows use the
user-friendly version. So, in the line in my code which uses the CDate
function...

ttltime2 = 24 * (CDate(TextBox2.Value) - CDate(TextBox1.Value))

what is happening is that the two CDate function calls convert the String
representations of time into faction-of-a-day decimal values, these
fractions are then subtracted to get a time difference and, finally, that
difference is multiplied by 24 to convert the fraction of a 24-hour day it
represents into actual hours (as a Double value).

Rick

 

[WLMPilot]

Rick, thank you so much for taking the time to explain it. I am still
having problems and do not complete understand your solution enough to figure
out how to correct, though I have tried. I copied/pasted the formulas and
adjusted the DIM to match yours. Here is what is going on now.

I input for Textbox1, 8:00 Textbox2, 17:23 same as (5:23pm)

I used Msgbox to check values of ttltime1 and ttltime2:
ttltime1 = 16:00
ttltime2 = -946624

The value (ttltime2) that should appear on the spreadsheet is 9.38. If you
subtract the above times:
17:23 - 8:00 = 9:23 (9 hrs & 23 min)
Convert to hours by somehow splitting the answer up: 9 + (23/60) = 9.38 hrs.

This is what I am looking for. I know you probably understand this, but I
am not getting the right answers. I tried to custom format to h.hh and #.##.
I figure h.hh would not work, but tried. I also changed the format you had
in your formula from h:nn to h:mm. Is that the way it should be?

Thanks for your continued help with this. It is greatly appreciated!!!

Les

 

[WLMPilot]

Rick, one more thing (just in case). I am entering the times in the
textboxes as 24 hr time, thus the 17:23 for 5:23pm.

Les

 

[Rick Rothstein \(MVP - VB\)]

No, entering in either 12-hour or 24-hour format is fine. I can't duplicate
your results... my code works for me exactly as I posted. Well, I can get
the 16 hour value you posted, but only if I enter the 2nd time as 1723
without a colon. I think the difference maybe in our set-ups of the
TextBoxes. Can you describe where you got the TextBoxes from, what (if
anything) they are linked to, what they are placed on (the worksheet
directly or a UserForm), copy/paste the exact code you are using and any
other information about your set-up that has to do with this part of your
problem? In other words, describe how you went about creating this portion
of your spreadsheet project.

Rick

 

[WLMPilot]

Rick, here is everything. Probably more than you needed, but should tell you
everything.

Spreadsheet Setup

B125:B176 = DATE
Date is the beginning date for 52 weeks, beginning on Mondays.
B125 = 12/17/07
B126 = 12/24/07
B127 = 12/31/07
Etc…..
B176 = 12/08/08

Columns C-G are the days of the week (Mon – Fri) for each B125:B176

First, user (me) clicks command button to display userform

Private Sub CommandButton11_Click()
TimeCalc
End Sub

'ROUTINE TO DISPLAY TIME USERFORM
Sub TimeCalc()
Dim TextBox1 As Date
Dim TextBox2, TextBox3 As Double
TimeCalcFm.Show
End Sub


Purpose of Userform: User enters the following:
Textbox1 = Date worked
Textbox2 = Time clocked in
Textbox3 = Time clocked out

When the ENTER button is clicked, the macro you have been helping me with
executes (see below). After all the calculations, the macro locates where
the time (in hours). It will loop, starting with row 125 and compare the
date entered (textbox1) against the date in spreadsheet (date1). When it
finds the first occurrence of Textbox1 < date1, then:
1) date1 = date in previous cell (column B, ie., i-1 in macro)
2) column is determined by (DateDiff("d", date1, TextBox1) + 3)
3) then ttltime2 is placed in correct day worked.
Locating the correct column works fine. Just cannot get the time down.

Private Sub CommandButton1_Click()
Dim i As Double
Dim date1 As Date
Dim ttltime1 As String
Dim ttltime2 As Double
ttltime2 = 24 * (CDate(TextBox2.Value) - CDate(TextBox1.Value))
ttltime1 = Format$(ttltime2 / 24, "h:mm")
MsgBox ttltime1
MsgBox ttltime2
With Worksheets("PAS")
For i = 125 To 176
date1 = .Cells(i, 2).Value
If TextBox1 < date1 Then
date1 = .Cells(i - 1, 2).Value
.Cells(i - 1, (DateDiff("d", date1, TextBox1) + 3)).Value =
ttltime2
i = 176
End If
Next
End With
End Sub

Les

 

[WLMPilot]

I had not heard back from you, but I kept trying after giving it a break and
noticed a typo. The calculation was using the wrong textbox. Once I
corrected it, it worked just like the formula you gave.

Thanks for your help!
Les

 

[Rick Rothstein \(MVP - VB\)]

I'm sorry I didn't get back to you; but, somehow, my newsreader stopped
showing this thread as having a pending message. In any case, I am glad you
got it worked out. Nice going!

Rick