userform textbox using vlookup date could not give correct answer (40850)

OscarPerez

New Member
Joined
Nov 5, 2009
Messages
42
Hi,

I created an UserForm that looks for Student Name his/her Conferenece Date through a VLookup.

The following is my code:

Private Sub cmdFind_Click()
With frmConference
.txtTranslator.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 6, 0)
.txtDate.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 5, 0)
.txtTime.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 4, 0)
End With
End Sub

The txtTranslator and txtTime works correctly, however txtDate give me a number '40850' not a Date?

How can I change this number for the actual Date?

Thank you in advance for your help!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi

40850 is actually a date (3/11/2011). It's the number of days since 1/1/1900.
This is the way Excel stores dates.

What are you going to do with this value? If you write it to a sheet cell formatted as date, it will display correctly.
 
Upvote 0
The textbox did display it as 40850 not as November 3, 2011, as the worksheet has it.

How can I have this format to display in the text box?
 
Upvote 0
Hello, try formatting the number with Format$(), e.g.,

Code:
MsgBox Format$(40850, "mmmm d, yyyy")
MsgBox Format$(40850 + 20, "mmmm d, yyyy")
 
Upvote 0
Thank you guys for your response!

Why isn't it displaying the Date that I already have in the worksheet cell F2, which is November 3, 2011 on the txtDate? It only displays as excel number 40850?

The VLookup is working correctly! :confused:
 
Upvote 0
Just format it. Excel stores dates and times as numbers, the Worksheet Cell is simply a formatted mask. So make your own mask for the UserForm, with Format$(), as shown.
 
Upvote 0
I already have the cell formatted as date.

The problem is not on the cell, it's on the Text Box on the UserForm, it's not Displaying the Date as Date only as Number! :confused:
 
Upvote 0
Ola Oscar
See this code, just an example...

Code:
Private Sub UserForm_Click()
Dim num&, datestring$
num = 40850
datestring = Format(num, "mmm d yyyy")
TextBox1.Text = datestring
End Sub
 
Upvote 0
Thanks Worf!

It works, but if the Date changes, e.g. November 11, 2011 or for next year, how I make it to follow the actual date? It would not always be 40850!

Gracias!
 
Upvote 0
The num variable can receive a value from another variable like in
num=mydate
or a value from a sheet like in this example:
Code:
Private Sub UserForm_Click()
    Dim num&, datestring$
    num = Sheets("Spanish").Range("b2").Value
    datestring = Format(num, "mmm d yyyy")
    TextBox1.Text = datestring
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top