VBA Userform "CDate" calculations on Times = Total Hrs

**Kitsa** · 11-05-2020, 05:07 PM

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?

HTML Code:

Cross posted at https://www.mrexcel.com/board/thread...l-hrs.1150373/ --6SJ

**Keebellah** · 11-05-2020, 05:20 PM

Hi,
When the time spans the midnight hout, you will have to do two calculations:

one from 23:59:59 - 09:00:00 and add the value from 00:00:00 + 02:00:00

**Kitsa** · 11-05-2020, 05:25 PM

Thanks Keebellah,
I'm not that good with VBA, How would I code this?

**Keebellah** · 11-05-2020, 05:42 PM

I will need to see how you've filled the values in the textboxes.
And when you show a vba example you expect others to assume that you do know something
I'll put it together and update

**Kitsa** · 11-05-2020, 06:03 PM

My Apologise,
Doe this help?

Private Sub cmdcalculate_Click()

TextBox1 = TextBox1.Text
TextBox2 = TextBox2.Text

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

End Sub

Private Sub cmdclearinfo_Click()

 TextBox1.Value = ""
 TextBox2.Value = ""
 TextBox3.Value = ""
 
 
End Sub

Private Sub cmdexist_Click()
  Unload UserForm1
End Sub


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   
   If Not Me.TextBox1 Like "??:??" Then
     MsgBox "Please use format 'hh:mm'"
     Cancel = True
     Exit Sub
   Else
   
  End If
     
   myVar = Application.WorksheetFunction.Text(Me.TextBox1, "hh:mm am/pm")
   
   Me.TextBox1 = myVar
   
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not Me.TextBox2 Like "??:??" Then
     MsgBox "Please use format 'hh:mm'"
     Cancel = True
     Exit Sub
   Else
   
  End If
     
   myVar = Application.WorksheetFunction.Text(Me.TextBox2, "hh:mm am/pm")
   
   Me.TextBox2 = myVar
End Sub

**6StringJazzer** · 11-05-2020, 06:04 PM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided. If you do not have permission to post the link, PM me.)

**Keebellah** · 11-05-2020, 06:08 PM

Hi, Sorry was typing my answer and didn't see your posting about the cross-posting. until after

**Keebellah** · 11-05-2020, 06:06 PM

Try this, paste this code in your Userform module

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Trim(TextBox1)) = 5 And Len(Trim(TextBox2)) = 5 Then Update_TextBox3
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '*  only numbers allowed
    Case Asc("0") To Asc("9")
    Case Asc(":")
        If Len(Trim(TextBox1)) <> 2 Then KeyAscii = 0: Exit Sub
    Case Else
        KeyAscii = 0
    End Select
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Trim(TextBox1)) = 5 And Len(Trim(TextBox2)) = 5 Then Update_TextBox3
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '*  only numbers allowed
    Case Asc("0") To Asc("9")
    Case Asc(":")
        If Len(Trim(TextBox2)) <> 2 Then KeyAscii = 0: Exit Sub
    Case Else
        KeyAscii = 0
    End Select
End Sub

Private Sub Update_TextBox3()
If Len(Trim(TextBox1)) <> 5 Or Len(Trim(TextBox2)) <> 5 Then Exit Sub
Dim tTime1  As Date
Dim tTime2  As Date
tTime1 = TimeSerial(Val(Left(TextBox1, 2)), Val(Right(TextBox1, 2)), 0)
tTime2 = TimeSerial(Val(Left(TextBox2, 2)), Val(Right(TextBox2, 2)), 0)

Select Case Hour(tTime2) < Hour(tTime1)
Case Is = False
    TextBox3 = Format(tTime1 - tTime2, "hh:mm")
Case Else
    TextBox3 = Format(TimeSerial(23, 59, 60) - tTime1 + tTime2, "hh:mm")
End Select

End Sub

**Kitsa** · 11-05-2020, 06:23 PM

Hi 6StringJazzer,
I'm sorry, I wasn't aware I was allowed to do this, I'm only new at this. I would share the link but it's won't let me. You ask to PM you, what does PM mean?

**6StringJazzer** · 11-05-2020, 06:47 PM

Originally Posted by Kitsa

I'm sorry, I wasn't aware I was allowed to do this, I'm only new at this. I would share the link but it's won't let me. You ask to PM you, what does PM mean?

I edited your first post to add the link for you.

PM means Private Message. To PM someone click on their name and then select Private Message from the pop-up window.

When you join any online forum, the first thing you should do is get familiar with their rules. Please take two minutes to review our rules. There aren't many, and they are all important.

**Kitsa** · 11-05-2020, 06:53 PM

Thanks and apologise again.

**Keebellah** · 11-05-2020, 07:12 PM

Just in case the code did not come throufg:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Trim(TextBox1)) = 5 And Len(Trim(TextBox2)) = 5 Then Update_TextBox3
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '*  only numbers allowed
    Case Asc("0") To Asc("9")
    Case Asc(":")
        If Len(Trim(TextBox1)) <> 2 Then KeyAscii = 0: Exit Sub
    Case Else
        KeyAscii = 0
    End Select
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Trim(TextBox1)) = 5 And Len(Trim(TextBox2)) = 5 Then Update_TextBox3
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '*  only numbers allowed
    Case Asc("0") To Asc("9")
    Case Asc(":")
        If Len(Trim(TextBox2)) <> 2 Then KeyAscii = 0: Exit Sub
    Case Else
        KeyAscii = 0
    End Select
End Sub

Private Sub Update_TextBox3()
If Len(Trim(TextBox1)) <> 5 Or Len(Trim(TextBox2)) <> 5 Then Exit Sub
Dim tTime1  As Date
Dim tTime2  As Date
tTime1 = TimeSerial(Val(Left(TextBox1, 2)), Val(Right(TextBox1, 2)), 0)
tTime2 = TimeSerial(Val(Left(TextBox2, 2)), Val(Right(TextBox2, 2)), 0)

Select Case Hour(tTime2) < Hour(tTime1)
Case Is = False
    TextBox3 = Format(tTime1 - tTime2, "hh:mm")
Case Else
    TextBox3 = Format(TimeSerial(23, 59, 60) - tTime1 + tTime2, "hh:mm")
End Select

End Sub

**Kitsa** · 11-05-2020, 08:15 PM

Hi Keebellah,
It came through and worked for me, but I would like it to come up 24:00 if I put start date 09:00AM to 09:00AM, at the moment it comes up 00:00. How can I do that?

**Keebellah** · 11-06-2020, 08:40 AM

Originally Posted by Kitsa

Hi Keebellah,
It came through and worked for me, but I would like it to come up 24:00 if I put start date 09:00AM to 09:00AM, at the moment it comes up 00:00. How can I do that?

Did you see my last post/answer?

**Kitsa** · 11-05-2020, 08:34 PM

Thank you everyone for helping. This is now solved. How do I mark this thread as solved?

**6StringJazzer** · 11-05-2020, 08:36 PM

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

VBA Userform "CDate" calculations on Times = Total Hrs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Re: VBA Userform "CDate" calculations on Times = Total Hrs

Thread Information

Users Browsing this Thread

Similar Threads

Coding Help - CDate & "Greater Than" Date

Short "Basic" Macro to copy and paste formulas "N" times.

[SOLVED] find a total of times a word appears across all the sheets in column "C"

Separate cell which have "Comma" into different row & multiple each row "n" times

"Fun times in no mans land" Translation - "Selective Duplication"

[SOLVED] Macro to delete entire row if cell contains the word "total" or "Total"

Linking two "total" pages to create a "Complete Total" page

Bookmarks

Bookmarks

Posting Permissions