VBA subtracting times using DTPicker

dido444

New Member
Joined
Mar 8, 2016
Messages
8
Hey all,

Another new guy question here. I'm trying to create a UserForm for people to enter information that includes a DTPicker that will be saved in a worksheet. What I'm trying to do is use the values of the DTPicker to get the total time between the two inputs, in total hours. I am trying to use the DateDiff function but it does not seem to be working for me. My code is very simple and I think there is an obvious answer in here, but I'm very new at VBA. Any help on how you guys subtract times would be greatly appreciated!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
I haven't got the DTP installed at work but could you post the code you are using, and what the issue is / which line of code the issues occurs?
 

MrIfOnly

Active Member
Joined
Nov 10, 2016
Messages
489
Try this on a new userform and see if it does what you are looking for in part or whole:

2 DTPickers with format: dtpShortDate named DTPicker1 and DTPicker2
2 DTPickers with format: dtpTime named DTPicker1T and DTPicker2T

then attach this code to a commandbutton:

Code:
Private Sub CommandButton1_Click()
Dim ddiff As Integer, hdiff As Double, mdiff As Double, tdiff As Double
    ddiff = (DTPicker2.Day - DTPicker1.Day) * 24
    hdiff = DTPicker2T.Hour - DTPicker1T.Hour
    mdiff = (DTPicker2T.Minute - DTPicker1T.Minute) / 60
    tdiff = ddiff + hdiff + mdiff
    
    MsgBox ddiff & vbNewLine & hdiff & vbNewLine & mdiff & vbNewLine & "=" & tdiff
End Sub
If it works in part, we can modify it from here. Let me know.

Regards,

CJ
 

dido444

New Member
Joined
Mar 8, 2016
Messages
8
CJ, that worked great, I was honestly just so unfamiliar with the DTP that I was unaware of the Hour/Minute syntax. I had to do some basic arithmetic to get the results I wanted on the output, but it helped debug my program. Thanks again! I'm sure I'll have more questions in the future as I learn more about VBA. You guys rock.

Best,

Mike
 

MrIfOnly

Active Member
Joined
Nov 10, 2016
Messages
489
Yeah, after getting some much needed sleep last night and looking at this again with fresh eyes, I realized the math was a bit 'fuzzy' when certain times were inputted. I'm glad you got it figured out though.

Regards,

CJ
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,067
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top