CODEWITHSUNDEEP
vbadatetimeexceldate-formatting
Robert Sim
Robert Sim

Reputation: 194

Excel VBA Storing time specific value into cell from userform

I have a userform that requires the user to input a specific date and time through two separate comboboxes, cboStartDate, cboStartTime. The user will also have to input the duration in a text field, txtDuration.

Upon saving, the start date and time will be stored in a formatted cell [DD/MM/YYYY HH:MM AM/PM]. The end date and time will be calculated from the duration field and stored in another cell with the same formatting. Something like this:

+-----------------------+-----------------------+
| startTime             | endTime               |
+-----------------------+-----------------------+
| 2/4/2012  11:30:00 AM | 2/4/2012  2:00:00 PM  |
+-----------------------+-----------------------+

However, after running the userform through, the start time is not stored, and the end time is not calculated. Something like this:

+-----------------------+-----------------------+
| startTime             | endTime               |
+-----------------------+-----------------------+
| 2/4/2012  12:00:00 AM | 2/4/2012  12:00:00 AM |
+-----------------------+-----------------------+

Below is my part of my VBA code:

Dim iRow As Long
Dim ws As Worksheet
Dim startDate As Date
Dim unFmtStartDuration() As String
Dim startDuration As Double
Dim minTest As Integer
Dim endDate As Date
Dim endDuration As Double

Set ws = Worksheets("EVENTS")

'Search for the last row in the worksheet
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Date manipulation and set start and end timings
unFmtStartDuration() = Split(cboStartTime.Text, ":")
startDuration = unFmtStartDuration(0)
If unFmtStartDuration(1) = "00" Then
    minTest = 0
Else
    minTest = unFmtStartDuration(1)
    If minTest = 30 Then
        startDuration = startDuration + 0.5
    End If
End If
startDate = DateValue(DateAdd("h", startDuration, cboDate.Text & " 12:00AM"))
ws.Cells(iRow, 4).Value = startDate
endDuration = txtDuration.Value
endDate = DateValue(DateAdd("h", endDuration, startDate))
ws.Cells(iRow, 5).Value = endDate

So how can I get this part sorted out? Would appreciate any help here. Thanks.

P.S. Would like to post screenshots here, but my reputation here is too low for it. Sorry.

Upvotes: 2

Views: 3493

Answers (1)

Gaffi
Gaffi

Reputation: 4367

It looks like you are only adding the time when minTest = 30, but this value probably varies quite a bit. Also, in one instance, you are comparing a string, and another a number when referencing unFmtStartDuration, which may work, but is confusing when reading your code.

To follow your current method, use

startDuration = Val(unFmtStartDuration(0) + Round(Val(unFmtStartDuration(1)) / 60, 2)

to replace this

startDuration = unFmtStartDuration(0)
If unFmtStartDuration(1) = "00" Then
    minTest = 0
Else
    minTest = unFmtStartDuration(1)
    If minTest = 30 Then
        startDuration = startDuration + 0.5
    End If
End If

This will take whatever the time is and convert it to the decimal form you are using, instead of relying on the 30 match. (Unless you need that specifically. If so, say so, as I think this can still be arranged with rounding tricks.)

However, I think a better option would be to use

startDuration = TimeValue(cboStartTime.Text) * 24

So no other math or checks are involved.

Also, unless cboStartTime.Text (and subsequently startDuration) is greater than 24 hours, this 

startDate = DateValue(DateAdd("h", startDuration, cboDate.Text & " 12:00AM"))

will always return the date specified in cboDate.Text with an implied 12:00:00 AM. To correct this, you will want to change to

startDate = DateAdd("h", startDuration, cboDate.Text & " 12:00AM")

I think there is some more to fix, but hopefully this gets you going in the right direction...

Upvotes: 2

Related Questions