Getting date from Userform (formatting issue)

[Sweedler]

Hello

I have a Userform that I am using to enter data into cells in a row. One of those cells is to include a date. I want the date to be formatted "YY-MM-DD" (January 5, 2020 = 20-01-05). When I have the value from the userform entered into the cell it does not act as the date that it visually has.

For example. I have entered: 21-06-06 (in cell A2) (Todays date = 23-06-19 (in cell H1)) ... =A2>P1 returns the value TRUE. Which it should clearly not.

I need help getting the formatting right, and perhaps putting in a check to make sure that there is a date entered as well.

 

[Sweedler]

@Sweedler
Are you sure @dmt32 code worked for you?
When in the textbox you enter 20-01-05 you mean it's January 5, 2020, right?
AFAIK, CDate function will read the last 2 digit as year not day.
So, if you enter "20-01-05" in the textbox then it will be read as "20-01-2005" instead of "2020-01-05"
I suggest you to use DateSerial function to make sure which part is year, month or day.

It did work well for me. I tested to make sure that it had a date value and it works.

 

[dmt32]

@Sweedler
Are you sure @dmt32 code worked for you?

Code was intended as suggestion only not a complete solution - I provided OP with link to enable selection of appropriate Function for their project

Dave

 

[Akuini]

It did work well for me. I tested to make sure that it had a date value and it works.

Ok, then can you show us the code?
I'm curious because it doesn't work for me on the example that I gave you.

 

[Sweedler]

Ok, then can you show us the code?
I'm curious because it doesn't work for me on the example that I gave you.

Here is the entire code for the portion that includes that bit of code.

Private Sub hcplaggtill_Click()

Me.Hide

Range("B2") = Application.WorksheetFunction.VLookup(cboCourseTee, Sheets("ADMIN").Range("A1:F1000"), 2, False)
Range("C2") = Application.WorksheetFunction.VLookup(cboCourseTee, Sheets("ADMIN").Range("A1:F1000"), 3, False)
Range("D2") = Application.WorksheetFunction.VLookup(cboCourseTee, Sheets("ADMIN").Range("A1:F1000"), 4, False)

Range("E2").Value = Me.hcpexakthcp.Value
Range("G2").Value = Me.hcpslag.Value
Range("H2").Value = Me.hcppoang.Value

Range("K2") = Application.WorksheetFunction.VLookup(cboCourseTee, Sheets("ADMIN").Range("A1:F1000"), 5, False)
Range("L2") = Application.WorksheetFunction.VLookup(cboCourseTee, Sheets("ADMIN").Range("A1:F1000"), 6, False)

With Range("A2")
.Value = CDate(Me.hcpar.Value)
.NumberFormat = "YY-MM-DD"
End With

Me.cboCourseTee = ""
Me.hcpar = ""
Me.hcpexakthcp = ""
Me.hcppoang = ""
Me.hcpslag = ""
Me.OptionButton1 = False
Me.OptionButton2 = False


End Sub


Not sure if the fact that I am in Sweden helps the programming/ code as year-month-day is our default

 

[Akuini]

Not sure if the fact that I am in Sweden helps the programming/ code as year-month-day is our default

Ah, I think that's why. you're excel date setting is year-month-day, I thought it was month-day-year or day-month-year.
Thank you for showing us the code & the explanation.