Userform submitting a date in format "mmm-yy", but a small problem arises.

[ashleyuk1984]

Hi,
I have a small problem, where no doubt the solution is smacking me straight in the face.

If you can imagine a userform, with a textbox, and a submit button.

The textbox needs to simply submit a date.

The user inputs the date as: Mar-14
Upon hitting submit, the date gets inserted into A1 as Mar-14 (which is correct - March 2014)... But the DAY is incorrect.
Ideally, I want it to be 01/03/2014 when I select the cell.
But instead it is shown as 14/03/2014 (I presume because of the year)
But how do I make it show 01/03/2014.

It's not causing any issues right now... But I want to fix it sooner rather than later.

How the userform submits the date.



How I WANT the userform to submit the date.



Like I said, the solution is smacking me right in the face.

The code that I'm using the format the date is:

Range("A" & LastRow).NumberFormat = "mmm-yy"

Ignore the columns and rows in the pictures... I'm using "A" to make things easier.

[Kenneth Hobs]

Textbox values are always strings. Did you use CDate() to convert the string to a date number before placing that value into the cell(s)?

[mancubus]

kenneth,

both CDate and DateValue functions return 14.3.2014 for "Mar-14".

[mancubus]

perhaps, stg like this.

Private Sub CommandButton1_Click()


    Range("A" & Rows.Count).End(xlUp)(2).Value = _
        DateSerial( _
            Right(TextBox1.Value, 2), _
            Application.Match(Left(TextBox1.Value, 3), Application.GetCustomListContents(3), 0), _
            1)
    
            'Right(TextBox1.Value, 2) = year
            'Application.Match(Left(TextBox1.Value, 3), Application.GetCustomListContents(3), 0) = month
            '1 = day


End Sub

ps: Built-in "custom list 3" is an array of abbreviated 12 months' names.

[david000]

This worked for me.

Private Sub CommandButton1_Click()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(lr, 1) = DateSerial(Year(TextBox1.Value), Month(TextBox1.Value), 1)
End Sub

[ashleyuk1984]

Sorry Kenneth, I thought I had replied to your comment yesterday. I obviously didn't hit the submit button??
My response was, no, I didn't use CDate and that I haven't actually heard of that term before. So I'll have to look it up sometime, always good to learn something new.

Thank you everyone for your help.
I ended up going for

DateSerial(Year(TextBox1.Value), Month(TextBox1.Value), 1)

Thanks David000

As this was the easiest to implement within my current code.
Problem solved.
Thanks again.

[mancubus]

you are welcome. the simpler, the better. actually my first attempt was stg like that. but i must have made a mistake, so could not produce the desired output. then i sailed away in vba ocean. .