VBA Userform "CDate" calculations on Times = Total Hrs

[kitsa]

Hi Guys,
I have a formula in my UserForm that give me h:mm, but the problem is I have staff that start at e.g. 09:00am and finishes at 02:00am, this formula I have give me return 7:00 hrs, when the return should be 17:00hrs. Does anyone know how to fix this?

Private Sub cmdcalculate_Click()

TextBox1 = TextBox1.Text
TextBox2 = TextBox2.Text

TextBox3 = Format(CDate(TextBox2) - CDate(TextBox1), "h:mm")

End Sub

 

[joeu2004]

Private Sub cmdcalculate_Click()
t1 = CDate(TextBox1.Text)
t2 = CDate(TextBox2.Text)
TextBox3 = Format(t2 - t1 - (t2 < t1), "h:mm")
End Sub

Note: -(t2<t1) actually adds 1 (1 day; 24 hours) when t2<t1 is true. The reason is: in VBA, a True value converts to -1, not to +1 as it does in Excel.

(FYI, t2-t1 is actually -7 hours; so, -7+24 = 17. Or as a time serial value, -7/24 + 1 = 17/24.)

PS.... You might want t2<=t1 if you want 9:00 AM to 9:00 AM to be treated as 24 hours, not zero.

 

[Fluff]

Cross posted VBA Userform "CDate" calculations on Times = Total Hrs

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[kitsa]

Hi Fluff,
Sorry I didn't realise you couldn't do this as it's a different site, but yes I have other info to help me with this VBA Userform "CDate" calculations on Times = Total Hrs

 

[Fluff]

You can do it, but in future you just need to let us know when you start the thread.

 

[kitsa]

Private Sub cmdcalculate_Click()
t1 = CDate(TextBox1.Text)
t2 = CDate(TextBox2.Text)
TextBox3 = Format(t2 - t1 - (t2 < t1), "h:mm")
End Sub

Note: -(t2<t1) actually adds 1 (1 day; 24 hours) when t2<t1 is true. The reason is: in VBA, a True value converts to -1, not to +1 as it does in Excel.

(FYI, t2-t1 is actually -7 hours; so, -7+24 = 17. Or as a time serial value, -7/24 + 1 = 17/24.)

PS.... You might want t2<=t1 if you want 9:00 AM to 9:00 AM to be treated as 24 hours, not zero.

Hi Joeu2004,
I have changed the code to t2<=t1 but when I do put the time in 09:00 AM to 09:00 AM, the value is still 00:00, what have I done wrong?

Private Sub cmdcalculate_Click()

t1 = CDate(TextBox1.Text)
t2 = CDate(TextBox2.Text)
TextBox3 = Format(t2 - t1 - (t2 <= t1), "h:mm")


End Sub

 

[joeu2004]

I have changed the code to t2<=t1 but when I do put the time in 09:00 AM to 09:00 AM, the value is still 00:00, what have I done wrong?

My bad! I'm not used to VBA Format. Changing "<" to "<=" was an afterthought, untested (sigh).

t2-t1-(t2<=t1) returns the correct value when t2=t1 is true, namely 1 (day; 24 hours).

The problem is: the hour and minute parts are effectively zero (i.e. 0/24). So "h:mm" displays 0:00.

I would use WorksheetFunction.Text and "[h]" in the format, as demonstrated below.

Private Sub cmdcalculate_Click()
tb1 = "9:00 AM"
tb2 = "9:00 AM"
t1 = CDate(tb1)
t2 = CDate(tb2)
MsgBox WorksheetFunction.Text(t2 - t1 - (t2 <= t1), "[h]:mm")
End Sub

"h" displays hours modulo 24 (i.e. 0 to 23). "[h]" displays the actual number of hours (24 and more).

So the code above displays 24:00.

 

[kitsa]

My bad! I'm not used to VBA Format. Changing "<" to "<=" was an afterthought, untested (sigh).

t2-t1-(t2<=t1) returns the correct value when t2=t1 is true, namely 1 (day; 24 hours).

The problem is: the hour and minute parts are effectively zero (i.e. 0/24). So "h:mm" displays 0:00.

I would use WorksheetFunction.Text and "[h]" in the format, as demonstrated below.

Private Sub cmdcalculate_Click()
tb1 = "9:00 AM"
tb2 = "9:00 AM"
t1 = CDate(tb1)
t2 = CDate(tb2)
MsgBox WorksheetFunction.Text(t2 - t1 - (t2 <= t1), "[h]:mm")
End Sub

"h" displays hours modulo 24 (i.e. 0 to 23). "[h]" displays the actual number of hours (24 and more).

So the code above displays 24:00.

Thank you so much, really really appreciate it. I finally worked out how you done it as per below:

Private Sub cmdcalculate_Click()

t1 = CDate(TextBox1.Text)
t2 = CDate(TextBox2.Text)
TextBox3 = WorksheetFunction.Text(t2 - t1 - (t2 <= t1), "[h]:mm")

End Sub

 

[kitsa]

Hi Fluff,
How do I put Solved on this thread?

 

[MARK858]

Hi Fluff,
How do I put Solved on this thread?

You don't need to but if you do want to mark a post as a solution then the instructions are in the link below

October 2020 Forum Update

Mark as solution Although the "Mark as solved" concept has never been used at the MrExcel Message Board since Bill launched the forum in 1998, the new feature "Mark as solution" is different in that marking a post as a solution does not lock the thread. This allows members to offer alternative...

www.mrexcel.com