Excel 2013-Display Date in MsgBox

[aliendan]

I have a command button where I input a well name in an input box. It returns the record in a msgbox. How do I get the date to display as a short date instead of a number.
This is the code I'm using:

Sub LOOKUP()
On Error GoTo MyErrorHandler:
Dim PRIMARY_WELLCOMP_SHORT_NAME As String

PRIMARY_WELLCOMP_SHORT_NAME = InputBox("Enter the Well Name :")
Det = "PRIMARY_WELLCOMP_SHORT_NAME : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 1, False)
Det = Det & vbNewLine & "WELL ANALYST : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 2, False)
Det = Det & vbNewLine & "OIL_RATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 3, False)
Det = Det & vbNewLine & "WATER_RATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 4, False)
Det = Det & vbNewLine & "GAS_RATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 5, False)
Det = Det & vbNewLine & "WELL_TEST_DATE : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False)
Det = Det & vbNewLine & "TEST_FACILITY : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 7, False)
Det = Det & vbNewLine & "BATTERY_NAME : " & Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 8, False)

MsgBox "Well Details : " & vbNewLine & Det

Exit Sub

MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Well Not Listed in the table."
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If

End Sub

This is the message box that gets returned:

Thank you in advance!

Dan Rogotzke

 

[combo]

Date is coded as number, convert value to text:
[tt]Det = Det & vbNewLine & "WELL_TEST_DATE : " & Format(Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False), "dd/mm/yyyy")[/tt]

combo

 

[Andrzejek]

... or:

[blue]Dim dat As Long

dat = [/blue]Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False)
Det = Det & vbNewLine & "WELL_TEST_DATE : " & [blue]CDate(dat)[/blue]

BTW - VBA questions (for Access) have better Forums here on TT

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[aliendan]

combo: ERROR. This is what I get:

Andrzejek: Data missing. This is what I get:

Dan Rogotzke

 

[Andrzejek]

OK, try this:

Dim dat As Long

dat = Application.WorksheetFunction.VLookup(PRIMARY_WELLCOMP_SHORT_NAME, Sheet1.Range("A2:H3219"), 6, False)[blue]
Debug.Print dat[/blue]
Det = Det & vbNewLine & "WELL_TEST_DATE : " & CDate(dat)

and report back what the Debug line shows.

From your original post, you had a value of 42984:

Dim dat As Long
dat = 42984
Debug.Print CDate(dat)

Immediate Window shows: [tt] 9/6/2017 [/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[combo]

This was replacement of the line of code with date, did you pasted as one line of code?

combo

 

[aliendan]

combo: I'LL BE DANGED! HAHA! I failed to see the "Format" you put in the string. When I copy/pasted the line IT WORKED. Thank you sooooo much!

Dan Rogotzke

 

[aliendan]

combo and Andrzejek,

I gotta tell ya, I'm an old fart now and I don't code near as much as I did years ago. And I've never coded in Excel before but I'm a little embarrassed to realize how simple the solution is. I don't know why I couldn't see it myself. I tried everything I could think of "except the correct thing". <Laughing loudly now>. I've forgotten way more than I remember these days. Thank you both for your help.

Dan

Dan Rogotzke

 

[Andrzejek]

2 points,
Don't forget to give combo a star (click on Great Post! link in his post)

And the reason you see a number instead of Date is because Excel (and data bases) treat/keep dates as numbers. The numbers is just formatted to show you as a Date. You may try it in Excel: in a cell type today's date: 11/3/2017 and you see a date. Now, change the cell format to General and you see 43042
That's how many days passed since 1/1/1900 (I think)


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Faq68-5827

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}