User form, VBA and timediff

[CuriousGeorg]

Ive built a userform where people fill in various information including dates.

what I need to happen is the user form copies all the information into a row (which it does fine). But also work out the difference in the 2 times entered in the userform.

Is this possible?


Thanks in advance

[Kenneth Hobs]

Welcome to the forum!

To do math, you need to convert the string values into numeric date values and then substract. e.g

Private Sub CommandButton1_Click()
MsgBox Format(CDate(TextBox1.Value) - CDate(TextBox2.Value), "hh:mm")
MsgBox CLng((CDate(TextBox1.Value) - CDate(TextBox2.Value)) * 24) & " hours."
End Sub

[CuriousGeorg]

Hi and thanks in advance

Ok, i think i get that!


RowCount = Worksheets("Sheet2").Range("A7").CurrentRegion.Rows.Count
With Worksheets("Sheet2").Range("A7")
.Offset(RowCount, 0).Value = Me.txtClaimnumber.Value
.Offset(RowCount, 1).Value = Me.cboRTM.Value
.Offset(RowCount, 2).Value = Me.cboassfrom.Value
.Offset(RowCount, 3).Value = DateValue(Me.incidentdate.Value)
.Offset(RowCount, 4).Value = DateValue(Me.fnoldate.Value) & " " & TimeValue(Me.fnoltime.Value)
.Offset(RowCount, 5).Value = DateValue(Me.pickupdate.Value) & " " & TimeValue(Me.pickuptime.Value)
.Offset(RowCount, 6).Value = Me.cboLiabfnol.Value
.Offset(RowCount, 7).Value = Me.cboTelFnol.Value
.Offset(RowCount, 8).Value = Me.cboAddressfnol.Value
.Offset(RowCount, 9).Value = Me.cboLiabtriage.Value
.Offset(RowCount, 10).Value = Me.cboaddresstriage.Value
.Offset(RowCount, 11).Value = Me.cboNumbertriage.Value
.Offset(RowCount, 12).Value = Me.cboassto.Value
.Offset(RowCount, 13).Value = DateValue(Me.handoffdate.Value) & " " & TimeValue(Me.handofftime.Value)

End With

is how the data from the user form pulls onto the worksheet. Is there a way I can pull the answer from this into say Rowcount, 14?


In a nutshell I want Rowcount, 14 to show the time difference between



DateValue(Me.fnoldate.Value) & " " & TimeValue(Me.fnoltime.Value) and DateValue(Me.pickupdate.Value) & " " & TimeValue(Me.pickuptime.Value)

[Kenneth Hobs]

I don't know your data. Is the data date, time, or datetime?

I would use CDBL to convert a datetime into a number and then just do the math. I don't see a need to breakout date and time values separately. Of course this depends on how you are getting the date/time values.

I guess it might go something like:

.Offset(RowCount, 14).Value = CDBL(CDate(fnoldate.Value)) - CDBL(CDate(pickupdate.Value)) Of course this is the number of days difference. Multiply by 24 to get the number of hours.

As you can see, the forum is having some problems with just posting code and not adding more code tags.

[CuriousGeorg]

ah yes I see.. basically.. Me.fnoldate is a calendar date field and me.fnoltime is the time.

It's placed like that so it's put into the workbook as one cell. This is all from a user form.

Assume it can do the same for example CDBL(CDate(fnoldate.value & fnoltime.value)) - CDBL(CDate(pickupdate.Value & pickuptime.Value))??


Im at home now and dont have the form to show at the moment.

I can try the above (which looks like a great start to what Im aiming for) and see the results i get? and if that fails post again?