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.

 

[ncvnrjn]

Hi,

if you format all date fields to YY-MM-DD it should work.

 

[Sweedler]

I have tried that, but for some reason it is not carrying over.

 

[Sweedler]

Hi,

if you format all date fields to YY-MM-DD it should work.
View attachment 93878

I tried that. But it was not working. Keeps coming out as a strange format. I have tried to remake my user form to enter the date in three separate boxes (year, month and day). I was thinking I could use "worksheetfunction.DATE" .. but date does not seem to be a formula that works there. I am out of ideas right now.

 

[dmt32]

Hi,
what comes out of your UserForms TextBox is Text - you should coerce the string in to its correct datatype using one of the Type Conversion Functions like CDate

example

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

Note: you will need to check for invalid date entry otherwise code line will error.

Dave

 

[Sweedler]

Hi,
what comes out of your UserForms TextBox is Text - you should coerce the string in to its correct datatype using one of the Type Conversion Functions like CDate

example

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

Note: you will need to check for invalid date entry otherwise code line will error.

Dave

That one worked really well. Thank you so much

 

[Sweedler]

Would something simila

That one worked really well. Thank you so much

Hi,
what comes out of your UserForms TextBox is Text - you should coerce the string in to its correct datatype using one of the Type Conversion Functions like CDate

example

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

Note: you will need to check for invalid date entry otherwise code line will error.

Dave

Would something similar be possible for the numbers coming out of the userform .. so that I do not get the "number stored as Text" error all the time?

 

[dmt32]

Would something similar be possible for the numbers coming out of the userform .. so that I do not get the "number stored as Text" error all the time?

Yes, just use the required type conversion function

example

Private Sub CommandButton2_Click()
     With Range("H1")
        .Value = CDbl(Me.TextBox1.Value)
        .NumberFormat = "YY-MM-DD"
     End With
End Sub

Function has same issue as CDate in that you need to check TextBox contains a valid numeric value otherwise it will error - or you could use VAL function which is more forgiving.

You can read more on Type Conversion Functions in Helpfile : Type conversion functions (VBA)

Dave

 

[dmt32]

That one worked really well. Thank you so much

most welcome glad resolved your issue & appreciate your feedback

Dave

 

[Akuini]

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