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

03-25-2014, 09:40 AM
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. :banghead:

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
03-25-2014, 12:45 PM
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)?

03-25-2014, 01:38 PM

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

03-25-2014, 01:45 PM
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), _

'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.

03-25-2014, 04:48 PM
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

03-26-2014, 02:11 AM
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.

03-26-2014, 02:32 AM
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. :whistle:. :shark: