Excel VBA Userform Help - Calculate time in hours and minutes between start and end time

[CAmelb]

Hi Members,

I am a new member and a beginner at Excel VBA coding using UserForms.

I would like to kindly ask for some help from fellow members.

In my Excel UserForm I have a 'Start Time' and 'End Time' which are both set using 'DTpicker' control.

I would like to know how to write the VBA code to calculate the time difference between the 'Start Time' and 'End Time', and show that value in Hours:Minutes in a TextBox on the same UserForm. (See below)



Thanks in advance.

CA

 

[AlphaFrog]

Maybe something like this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] DTPicker1_Change()
    [color=darkblue]Call[/color] Update_Total_Time
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Private[/color] [color=darkblue]Sub[/color] DTPicker2_Change()
    [color=darkblue]Call[/color] Update_Total_Time
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    [color=darkblue]Call[/color] Update_Total_Time
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Update_Total_Time()
    [color=darkblue]If[/color] Me.DTPicker1.Value < Me.DTPicker2.Value [color=darkblue]Then[/color]
        Me.TextBox1 = Format(Me.DTPicker2.Value - Me.DTPicker1.Value, "HH:MM")
    [color=darkblue]Else[/color]
        Me.TextBox1 = "00:00"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[CAmelb]

Hi AlphaFrog,

Thank you for your reply and assistance with my enquiry.

The code you provided worked successfully.

Thank you for your help. Much appreciated.

CA

 

[KS Jayhawk]

Hi! I'm building a volunteer management database for an animal shelter at which I volunteer. I am trying to accomplish this same thing... but this solution is not working for me. I'm able to get the 00:00 to appear in the calculation TextBox, but I get an error when trying to do the calculation. I did not originally set up my user form to use a date picker and thought that might be causing the problem. However, I don't have date picker as a field option. I researched this and tried to add it through Developer > Insert > ActiveX > More Tools but do not have it as an option. From what I've read, it's possible I cannot use this feature because I'm operating on Windows 8.1 Pro 64-bit. (This seems weird to me... but I saw it in several sources saying 64-bit cannot load date picker...) I think the problem is that the formula cannot read time - but being somewhat new to VBA, I'm still confused on some of the code regarding Dim String, etc. and how to work through this.

I would appreciate any help you could provide. Thank you!!!

Maybe something like this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DTPicker1_Change()
    [COLOR=darkblue]Call[/COLOR] Update_Total_Time
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DTPicker2_Change()
    [COLOR=darkblue]Call[/COLOR] Update_Total_Time
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=darkblue]Call[/COLOR] Update_Total_Time
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Update_Total_Time()
    [COLOR=darkblue]If[/COLOR] Me.DTPicker1.Value < Me.DTPicker2.Value [COLOR=darkblue]Then[/COLOR]
        Me.TextBox1 = Format(Me.DTPicker2.Value - Me.DTPicker1.Value, "HH:MM")
    [COLOR=darkblue]Else[/COLOR]
        Me.TextBox1 = "00:00"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[AlphaFrog]

I did not originally set up my user form to use a date picker

What did you use instead of a date picker?

Show your code.

 

[KS Jayhawk]

Show your code.

Hi - thanks for your reply. I'm sorry for my lack of information. I used text boxes. It seems as though I might need to do some Dim String type work... but I haven't entirely figured out how that translates into time.

When using the code below, I get a Run-time error '13': Type mismatch. I have another set of code that I've neutralized right now (but included below). It doesn't error but also doesn't work except for the 00:00 appearing in TextBox 3.

I substituted Change with BeforeUpdate to reduce the amount of work the code is doing.

'GOOD CODE


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Call Update_Total_Time
End Sub


Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   Call Update_Total_Time
End Sub

'TEST CODE


Private Sub Update_Total_Time()


    If Me.TextBox1.Value < Me.TextBox2.Value Then
        Format(TextBox3, "hh:mm") = TextBox2 - TextBox1
    Else
        Me.TextBox3 = "00:00"
    End If
End Sub

'QUESTIONABLE CODE - not calculating, but not erroring


'Private Sub Update_Total_Time()


'    If Me.TextBox1.Value < Me.TextBox2.Value Then
'        TextBox3 = Format(DateDiff("n", TextBox1, TextBox2), "hh:mm")
'    Else
'        Me.TextBox3 = "00:00"
'    End If
'End Sub

 

[AlphaFrog]

This link describes how Excel calculates serial Dates and Time.
How Excel Stores Dates And Times

The TextBoxes contain text. Use the TimeValue function to convert text-time into serial-time.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Update_Total_Time()
    [color=darkblue]If[/color] Me.TextBox1.Value = "" [color=darkblue]Or[/color] Me.TextBox2.Value = "" [color=darkblue]Then[/color]
        Me.TextBox3.Value = "00:00"
    [color=darkblue]ElseIf[/color] [color=darkblue]Not[/color] IsDate(Me.TextBox1.Value) [color=darkblue]Then[/color]
        MsgBox "Invalid 'Start' time", vbExclamation, "Invalid Entry"
        Me.TextBox3.Value = "00:00"
    [color=darkblue]ElseIf[/color] [color=darkblue]Not[/color] IsDate(Me.TextBox2.Value) [color=darkblue]Then[/color]
        MsgBox "Invalid 'End' time", vbExclamation, "Invalid Entry"
        Me.TextBox3.Value = "00:00"
    [color=darkblue]ElseIf[/color] TimeValue(TextBox2.Value) < TimeValue(TextBox1.Value) [color=darkblue]Then[/color]
        MsgBox "'Sart' time occurs after 'End' time.", vbExclamation, "Invalid Entries"
        Me.TextBox3.Value = "00:00"
    [color=darkblue]Else[/color]
        Me.TextBox3.Value = Format(TimeValue(Me.TextBox2.Value) - TimeValue(Me.TextBox1.Value), "hh:mm")
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

Note: the result in TextBox3 will have the time value as text because its a textbox. So if you use that result elsewhere, you will have to convert it to serial time using the TimeValue function.

 

[KS Jayhawk]

This link describes how Excel calculates serial Dates and Time.
How Excel Stores Dates And Times

The TextBoxes contain text. Use the TimeValue function to convert text-time into serial-time.

Thank you so much! This works wonderfully!! (I'm sorry it took so long to reply, but as a volunteer project I work on this as time permits.) I also appreciate the additional information on dates and times. Still find it a little confusing but definitely understand it better.

If possible, how would I change this to include AM and PM? We have a midday volunteer shift and it is very common for volunteers to arrive in the 11 or 12 o'clock hour and leave after 1pm. As is, the code considers this type of situation as "Start time before End time". Or is the best way to handle this as "military time", which calculates correctly with the existing code?

 

[AlphaFrog]

The user can type in the textboxes either military time or AM/PM e.g.; the code will recognize 13:00 or 1:00 PM as valid entries.

 

[KS Jayhawk]

Note: the result in TextBox3 will have the time value as text because its a textbox. So if you use that result elsewhere, you will have to convert it to serial time using the TimeValue function.

Thanks! I ran it with AM / PM and saw it worked great as well.

Now back to this TimeValue thing. I've now got my entire userform working wonderfully and transferring the data to the spreadsheet so I moved into a test userform to try and work out TimeValue. I have yet to find a good example of how to do this. Based on what I've found, I think I'm going to be looking at something like this:

Dim TimeFormat As Date
TimeFormat = TimeValue("hh:nn:ss AMPM")

I've also seen this approach:

[COLOR=#C7254E][FONT=Menlo]application.worksheetfunction.timevalue(TimeFormat)
[/FONT][/COLOR]

But I'm not sure how this knows to interact with the specific text boxes (or cells once transferred to the spreadsheet) that contain time. The other defined variables I've used so far in this short point in my coding career have been to create variables based on 1 cell content or combining two specific text fields. Here is the code I'm using to transfer the time fields from the UF to the spread.

Sheets("2019 VolunteerTime").Range("Time_Start").Offset(TargetRow, 7).Value = Text_TimeIn 'time in
Sheets("2019 VolunteerTime").Range("Time_Start").Offset(TargetRow, 8).Value = Text_TimeOut 'time out
Sheets("2019 VolunteerTime").Range("Time_Start").Offset(TargetRow, 9).Value = Text_CalcTime 'total time

Do I need to take one of the two pieces of code about TimeValue and weave it into each of these lines?