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

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)



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

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

"Bob Phillips" wrote:

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)



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

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

"Bob Phillips" wrote:

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)



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

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)" wrote:

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

"Bob Phillips" wrote:

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)



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

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


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)" wrote:

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

"Bob Phillips" wrote:

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)



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

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)" wrote:

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

"Bob Phillips" wrote:

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)



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

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

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

"Bob Phillips" wrote:

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)



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

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

Mike F

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

You have other responses at your other posts.

WLMPilot wrote:

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

--

Dave Peterson