VBA subtracting times using DTPicker

[dido444]

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!

 

[gallen]

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]

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:

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]

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]

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