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]

Not sure what your latest question is, but I think you want something like this...

Dim TimeFormat As Date
TimeFormat = TimeValue(TextBox1.Value)

Or this...

Sheets("2019 VolunteerTime").Range("Time_Start").Offset(TargetRow, 7).Value = [B]TimeValue([/B]Text_TimeIn[B])[/B] [COLOR="#008000"]'time in[/COLOR]

Use the same syntax for the other textboxes.

 

[KS Jayhawk]

Thank you! Thank you! Thank you! I've been able to start entering and testing in real data. I've found an issue with this last bit of code... it works great except that when it transfers Total Time it transfers it as A TIME, like 12:##:00 AM... It appears that it will calculate correctly... but it's definitely confusing.

For example

If I put 6:04 PM in Time In and 6:27 PM in Time Out in the UF, which generates a correct 00:23 in Total Time in the UF, I get 12:23:00 AM transferred to the spreadsheet.

Is there a way to transfer that 00:23 to the spreadsheet in such a way that it can be added together with the other Total Times to see a variety of ways in which time will be reported. Or is the 60 minutes in an hour thing just too complex to make the curve? I think what it is producing is functional - but I'm trying to write it so that anyone can sit down and use it and I'm afraid this will cause problems. I can always put a note in the sheet if it is too much to ask.

 

[AlphaFrog]

- Right-click on the cell(s) that contain the time value and select Format Cells from the pop-up context menu
- Change the Number format to Custom and type in [hh]:mm

Or have the code set the cell format when it writes the time value e.g.

Range("A1").Value = Timevalue(Textbox1.Value)
Range("A1").NumberFormat = "[hh]:mm"